Язык QBE

     Для того чтобы выбрать необходимые  данные сразу из нескольких таблиц, следует подготовить запрос на выборку  данных; в окне запроса будут представлены требуемые таблицы, а критерий отбора будет определен в сетке QBE. Например, для выборки имени и фамилии  владельцев объектов недвижимости с  указанием учетных номеров принадлежащих  им объектов, а также названий городов, в которых эти объекты расположены, следует создать запрос. Списки полей  целевых таблиц запроса (а именно таблиц Owner и Property_for_Rent) размещены в  окне запроса над сеткой QBE.

     В результирующую таблицу запроса  из таблицы Owner выбираются столбцы Fname и Lname, а из таблицы Property_for_Rent – столбцы  Вnо и City. После выполнения данного  запроса будет выведена сетка  данных, содержащая записи, отвечающие указанному критерию.

     Многотабличный  запрос⁴, является примером запроса с внутренним (естественным) соединением.

     Если  в окно запроса помещается больше одной целевой таблицы, следует  убедиться, что списки их полей связаны  между собой линиями соединения, т.е. СУБД Microsoft Access знает, как соединить  таблицы друг с другом. Обратите внимание, а СУБД Access поместила над  верхней частью линии соединения. Символ 1 указывает на единичную  сторону связи типа “один ко многим”. Над нижней частью линии соединения помещен символ °°, отмечающий множественную  сторону этой же связи. Это означает, что в нашем примере один владелец может владеть многими объектами недвижимости, сдаваемыми в аренду.

     Если  СУБД Access не выполнила соединение таблиц автоматически или если между  данными таблицами еще не была описана какая-либо связь, то таблицы  в окне запроса не будут связаны  линией соединения. Возможность выборки  связанных данных из двух таблиц сохраняется, но потребуется указать способ соединения этих таблиц непосредственно при  создании запроса в окне сетки QBE. Однако для того чтобы две таблицы  можно было соединить непосредственно  в запросе, в обеих таблицах должны существовать связанные поля. В примере, поле Ono (Личный номер владельца) является общим для обеих таблиц – Owner и  Ргорerty_for_Rent. Для того чтобы соединение работало корректно, оба столбца  должны содержать одинаковые значения в связанных записях данных.

     Достаточно  часто появляется необходимость  обобщения той или иной группы данных. Например, сколько сдаваемых  в аренду объектов имеется в каждом из городов? Чему равна средняя заработная плата работников компании?

     в  SELECT OWNER.FName, OWNER.LName,

     PROPERTY_FOR_RENT.Pno,PROPERTY_FOR_RENT.City FROM OWNER INNER JOIN PROPERTY.FOR.RENT ON

     OWNER.Ono = PROPERTY_FOR_RENT.Ono;

     Операцию  обобщающих вычислений над группой  записей можно выполнить с  помощью запросов с подведением  итогов (иногда их называют обобщающими  запросами). СУБД Microsoft Access позволяет  производить различные типы итоговых вычислений, включая функции суммирования (Sum), вычисления среднего значения (Avg), поиска минимального (Min) или максимального (Мах) значения, а также подсчета экземпляров (Count). Чтобы получить доступ к этим функциям, следует изменить тип запроса на Totals, в результате чего в сетке QBE будет отображена дополнительная строка с названием Total. После выполнения обобщающего запроса отображается таблица, содержащая моментальный снимок состояния данных – набор строк, который не допускает внесения изменений.

     Как и в случае запросов других типов, в запросе с подведением итогов может быть указан некоторый критерий отбора записей. Например, предположим, что требуется определить общее  количество объектов недвижимости, сдаваемых  в аренду в каждом из городов. Для  этого необходимо сначала сгруппировать  данные об объектах по значению поля City, для чего используется функция Group By, после чего выполнить для каждой группы вычисление итогового значения с помощью функции Count. Общий вид  сетки QBE с подобным запросом, а результирующая сетка данных.

     SELECT PROPERTY_FOR_RENT.CIty, Count (PROPERTY_FOR_RENT.Pno) AS CountOfPno FROM PROPERTY_FOR_RENT  GROUP BY PROPERTY_FOR_RENT.City; 

     2.1 Более сложные  типы QBE запросов 

     Параметрические запросы позволяют вывести одно или более заранее определенных диалоговых окон, предназначенных для  ввода пользователем конкретных значений параметров запроса (критериев). Параметрические запросы создаются  посредством ввода в ячейку Criteria текста обращения к пользователю, помещенного в квадратные скобки. Эти действия выполняются для  каждого столбца, значение которого должно указываться в качестве параметра. Например, предположим, что требуется  так доработать запрос, чтобы пользователь мог ввести имя и фамилию владельца  объектов недвижимости, для которого необходимо вызвать сведения о принадлежащих  ему объектах. Сетка QBE с подобным параметрическим запросом. Для выборки сведений об объектах недвижимости, принадлежащих владельцу с именем ‘Carol Farrel’, необходимо ввести соответствующие значения имени и фамилии владельца в первое и второе диалоговые окна. Содержимое полученной в результате выполнения данного запроса сетки данных, а эквивалентный оператор запроса.

     Перекрестные  запросы могут использоваться для  обобщения обрабатываемых данных и  отображения их в формате компактной электронной таблицы. Этот формат  более наглядно представляет большой  объем данных с целью выявления  существующих  тенденций и проведения сравнительного анализа. Результирующая перекрестного запроса представляет собой моментальный снимок состояния  и не позволяет выполнять их обновление.

     Для создания перекрестных запросов можно  воспользоваться мастером CrossTab Query Wizard или же определить его собственными силами в сетке QBE. Создание перекрестного  запроса напоминает создание запросов с подведением итогов, однако теперь дополнительно потребуется указать  поля, которые будут использоваться как заголовки столбцов и строк, а также поля, содержащие исходные значения данных.

     Запросы на выборку дубликатов 

     По  результатам запроса типа Find Duplicates (Поиск дубликатов) можно сделать  заключение о наличии в таблице  дублирующихся записей, а также  определить, какие записи таблицы  содержат одно и то же значение в  некотором столбце. Например, можно  выполнить поиск дублирующихся  значений в поле адреса, что позволит определить наличие в базе нескольких записей об одном и том же владельце  объектов недвижимости. В то же время  можно выполнить поиск дублирующихся  значений в поле City, что позволит получить сведения о владельцах недвижимости, проживающих в одном и том  же городе.

     Предположим, что в какой-то момент непреднамеренно  была повторно создана запись о владельце  объектов недвижимости по имени ‘Carol Farrel’, причем этой записи был присвоен собственный уникальный номер владельца. В результате в базе данных появилось  две записи с различными уникальными  номерами владельца, описывающие одного и того же человека. Для выявления  подобной ситуации можно воспользоваться запросом на выборку дубликатов, созданным с помощью мастера Find Duplicates Query Wizard, доступ к которому можно получить в диалоговом окне. В этом запросе отбор записей будет вестись по совпадающим значениям в указанных полях.

     Запросы на выборку записей, не имеющих соответствия

     С помощью мастера Find Unmatched Query Wizard, доступ к которому осуществляется из диалогового  окна, можно отыскать все записи указанной таблицы, которые не имеют  связанных записей в другой таблице. Например, можно выбрать сведения о тех арендаторах, которые еще  не осматривали каких-либо сдаваемых  в аренду объектов недвижимости, посредством  сравнения записей таблиц Renter и Viewing. Мастер создаст запрос на основе предоставленных  ему ответов.

     Запросы с автоподстановкой могут использоваться для автоматического помещения  значений в определенные поля вновь  создаваемых записей. При вводе  в окне запроса или в окне созданной  на базе этого запроса формы некоторого значения в поле, используемое для  соединения двух таблиц, СУБД Microsoft Access автоматически отыщет и поместит в указанное место информацию, соответствующую введенному пользователем  значению. Если нам известно значение, которое должно быть помещено в поле, используемое для соединения таблиц, например, поле личного номера работника (Sno), используемое для соединения таблиц Property_for_Rent и Staff, то после ввода требуемого личного номера работника СУБД автоматически  заполнит оставшиеся поля информацией  о данном работнике. Если для веденного  значения не будет найдено соответствующей  записи, СУБД выведет сообщение об ошибке.

     Для создания запроса с автоподстановкой следует поместить в сетку QBE две  таблицы, между которыми существует связь типа “один ко многим”, после  чего указать поля, которые должны быть помещены в результирующую сетку  запроса. Поле соединения должно быть выбрано из таблицы, соответствующей  множественной стороне связи.

     2.2 Активные запросы 

     При создании запроса СУБД Microsoft Access обычно создает запрос на выборку данных, если только в меню Query не будет выбран какой-либо другой тип запроса. После  выполнения запроса на выборку СУБД отображает его результирующую сетку  данных. Если эта сетка допускает  обновление содержащихся в ней данных, то требуемые изменения можно  вносить непосредственно в результаты выполнения запроса, однако в этом случае записи можно будет модифицировать только поодиночке, последовательно, одну за другой.

     В случае, если необходимо выполнить  большое количество сходных изменений, время выполнения задания можно  существенно сократить, используя  активный запрос, который позволяет  вносить изменения сразу в  несколько записей. Существует четыре типа активных запросов: запросы создания таблиц, запросы удаления, запросы  обновления и запросы добавления записей.

     Активные  запросы создания таблиц позволяют  создавать новые таблицы на базе всех или части данных одной или  нескольких уже существующих таблиц. Вновь созданная таблица может  быть сохранена в текущей открытой базе данных или экспортирована в  другую базу данных. Отметим, что данные в новой таблице не наследуют  свойств полей исходных таблиц, включая  и определение первичного ключа.  Запросы создания таблиц могут быть полезны во многих случаях, например, для сбора исторической информации, создания моментальных снимков состояния  данных или для увеличения производительности программ форм и отчетов, использующих многотабличные запросы.

     Предположим, что требуется создать новую  таблицу StaffCut, которая должна содержать  столбцы Sno, FName, LName, Position и Salary, заполненные  данными из существующей таблицы Staff. Прежде всего необходимо подготовить  запрос, предназначенный для выбора указанных полей из таблицы Staff. Затем  в режиме Design View следует изменить тип созданного запроса на Make-Table, в результате чего на экран будет  выведено диалоговое окно. Это диалоговое окно содержит предложение указать имя и местоположение новой таблицы. Сетка QBE с подготовленным активным запросом создания таблицы. После запуска запроса на выполнение СУБД выведет предупреждающее сообщение с предложением указать, следует ли продолжить операцию создания новой таблицы. Вид этого сообщения. Если создание таблицы будет продолжено, СУБД создаст новую таблицу с именем StaffCut.

     Активные  запросы удаления предназначены  для удаления групп записей из одной или более таблиц. Один запрос удаления может использоваться для  удаления записей из одной таблицы; из нескольких таблиц, между которыми существует связь типа “один к  одному”; из нескольких таблиц, между  которыми существует отношение “один  ко многим”, но только в том случае, если установленные правила поддержки  ссылочной целостности разрешают  каскадное обновление.

     Например, предположим, что требуется удалить  все сведения об объектах недвижимости, расположенных в городе Глазго, а  также все связанные с ними записи об осмотрах. Для выполнения этой операции прежде всего следует  создать запрос, предназначенный  для выборки соответствующих  сведений из таблицы Property_for_Rent. Затем  в режиме Design View тип запроса должен быть изменен на Delete. Сетка QBE с созданным  активным запросом удаления. Поскольку  между таблицами Property_for_Rent и Viewing существует связь типа “один ко многим”, причем для нее установлено правило  поддержки ссылочной целостности Cascade Delete Related records, будут удалены  все строки таблицы Viewing, содержащие сведения об осмотрах объектов, расположенных  в городе Глазго. При запуске запроса  на выполнение система выведет предупреждающее  сообщение, предлагающее подтвердить  необходимость продолжения операции удаления. Общий вид окна этого  сообщения. Если выполнение операции удаления будет продолжено, система удалит все записи таблицы Property for Rent, отвечающие заданному условию, а также все  связанные с ними записи таблицы Viewing.

     Активные  запросы ⁵обновления выполняют глобальные обновления в группах записей одной или более таблиц. Например, предположим, что арендную плату за все сдаваемые в аренду объекты необходимо увеличить на 10%. Для выполнения подобного обновления прежде всего надо создать запрос на выборку данных из таблицы Propery_for_Rent. Затем в режиме Design View следует изменить тип запроса на Update. В ячейку Update To столбца Rent необходимо поместить выражение ‘[Rent]*l.l’. После запуска запроса на выполнение система выведет предупреждающее сообщение, которое содержит предложение подтвердить необходимость выполнения операции обновления. Если выполнение операции будет продолжено, система обновит значения в столбце Rent таблицы Property for Rent .