Язык QBE

Для того чтобы выбрать необходимые данные сразу из нескольких таблиц, ледует подготовить запрос на выборку данных; в окне запроса будут представлены все требуемые таблицы, а критерий отбора будет определен в сетке QBE.

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

В результирующую таблицу запроса из таблицы PrivateOwner выбираются столбцы fName и INarae, а из таблицы PropertyForRent — столбцы property-No и city.

После выполнения данного запроса будет выведена сетка данных, содержащая записи, отвечающие указанному критерию. Многотабличный является примером запроса с внутренним (естественным) соединением[3]. Если в окно запроса на выборку помещается больше одной исходной таблицы, следует убедиться, что списки их полей связаны между собой линиями соединения, т.е. СУБД Microsoft Access имеет информацию о том, как соединить таблицы друг с другом. Обратите внимание, СУБД Access поместила над верхней частью линии соединения символ 1, указывающий на единичную сторону связи типа "один ко многим". Над нижней частью линии соединения помещен символ ∞, отмечающий множественную сторону этой же связи. Это означает, что в примере один владелец может владеть многими объектами недвижимости, сдаваемыми в аренду.

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

СУБД Microsoft Access не обеспечивает автоматическое соединение таблиц, если взаимосвязанные данные находятся в полях с разными именами. Но при создании запроса существует возможность указать общие поля двух таблиц в сетке QBE.

1.3 Запросы с обобщением

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

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

Для выполнения некоторых вычислений может потребоваться подготовить собственное выражение. Например, предположим, что требуется вычислить сумму годовой арендной платы для каждого из объектов недвижимости, сведения о котором имеются в таблице PropertyForRent, с указанием значений номера объекта (propertyNo), города (city) и типа объекта (type). Сумма годовой арендной платы для каждого из объектов вычисляется посредством умножения суммы месячной арендной платы на количество месяцев в году. Для проведения этих вычислений в отдельный столбец сетки QBE следует поместить выражение 'Yearly ren:'. В этом выражении часть 'Yearly rent:' представляет собой имя нового столбца, а остальная часть ('[rent ]*12')задает формулу вычисления помещаемых в него значений как произведение значения поля rent каждой записи. Статистические функции SQL представлены в приложении Б.

1.4 Сложные типы запросов QBE

В СУБД Microsoft Access предусмотрен целый ряд усовершенствованных запросов. Рассмотрим наиболее применяемые из этих запросов, включая следующие[4]:

                  параметрические запросы;

                  перекрестные запросы;

                  запросы на выборку дубликатов;

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

                  запросы с автоподстановкой.

Параметрические запросы - запросы позволяют вывести одно или несколько заранее определенных диалоговых окон, предназначенных для ввода пользователем конкретных значений параметров запроса (критериев). Параметрические запросы создаются посредством ввода в ячейку Criteria (Условие отбора) текста обращения к пользователю, заключенного в квадратные скобки. Эти действия выполняются для каждого столбца, значение которого должно указываться в качестве параметра. Например, предположим, что требуется так доработать запрос, чтобы пользователь мог ввести имя и фамилию владельца, для которого необходимо выбрать сведения о принадлежащих ему объектах недвижимости. Для выборки сведений об объектах недвижимости, принадлежащих владельцу с именем 'Carol Parrel', необходимо ввести соответствующие значения имени и фамилии владельца в первое и второе диалоговые окна. Пример параметрического запроса представлен в приложения В.

Перекрестные запросы - запросы могут использоваться для обобщения обрабатываемых данных и отображения их в формате компактной электронной таблицы. Этот формат позволяет более наглядно представить большой объем данных с целью выявления существующих тенденций и проведения сравнительного анализа. Результирующая сетка перекрестного запроса представляет собой моментальный снимок состояния данных и не позволяет выполнять их обновление. Для создания перекрестных запросов можно воспользоваться мастером CrossTab Query Wizard (Перекрестный запрос)или же определить его самостоятельно в сетке QBE. Создание перекрестного запроса напоминает создание запросов с подведением итогов, однако теперь требуется дополнительно указать поля, которые будут использоваться как заголовки столбцов и строк, а также поля, содержащиеисходные значения данных.

Например, необходимо определить количество объектов недвижимости, за которые отвечает каждый работник компании, с указанием типа недвижимости. Для повышения наглядности результатов выполнения данного запроса мы поместили в таблицу PropertyForRent несколько новых записей об объектах недвижимости. При подготовке данного запроса прежде всего следует создать запрос с подведением итогов. В результате его выполнения будет получена сетка данных. Однако формат представления результатов запроса неудобен для проведения сравнительного анализа данных по каждому из работников. Для преобразования запроса на выборку данных в перекрестный запрос следует изменить тип запроса на Crosstab (Перекрестный), в результате чего в сетку QBE будет помещена дополнительная строка Crosstab (Перекрестная таблица).

Теперь появилась возможность указать поля, значения которых будут использоваться как заголовки столбцов и строк или же как исходные данные для суммирования. После выполнения новой версии запроса сетка с результатами будет более компактной. Ее формат позволяет легко проводить сравнительный анализ показателей отдельных сотрудников компании.

Запросы на выборку дубликатов - результат запроса типа Find Duplicates (Повторяющиеся записи) можно сделать заключение о наличии в таблице повторяющихся записей, а также определить, какие записи таблицы содержат одно и то же значение в некотором столбце. Например, можно выполнить поиск повторяющихся значений в поле адреса, что позволит определить наличие в базе нескольких записей об одном и том же владельце объектов недвижимости. В то же время можно выполнить поиск повторяющихся значений в поле city, что позволит получить сведения о владельцах недвижимости, проживающих в одном городе. Предположим, что в какой-то момент была непреднамеренно повторно создана запись о владельце объектов недвижимости по имени 'Carol Parrel', причем этой записи был присвоен собственный уникальный номер владельца. В результате в базе данных появились две записи с различными уникальными номерами владельца, описывающие одного и того же человека. Для выявления подобной ситуации можно воспользоваться запросом на выборку дубликатов, созданным с помощью мастера Find Duplicates Query Wizard (Повторяющиеся записи), доступ к которому можно получить в диалоговом окне. В этом запросе отбор записей будет вестись по совпадающим значениям в указанных полях (в нашем примере мы для простоты ограничимся полями fName и IName). Как уже упоминалось выше, мастер создает запрос на основе ответов, предоставленных пользователем на заданные этим мастером вопросы. Прежде чем выполнить вновь созданный запрос, имеет смысл посмотреть на сетку QBE с данным запросом на выборку повторяющихся значений. Результирующая сетка данных запроса, содержащая две строки о владельце по имени 'Carol Parrel'. Обратите внимание, что в последнем случае оператор SQL отображен полностью, включая и вложенный оператор SQL SELECT.

Запросы на выборку записей, не имеющих соответствия - с помощью мастера Find Unmatched Query Wizard (Записи без подчиненных), доступ к которому осуществляется из диалогового окна, можно отыскать все записи указанной таблицы, которые не имеют связанных записей в другой таблице. Например, можно выбрать сведения о тех арендаторах, которые еще не осматривали каких-либо сдаваемых в аренду объектов недвижимости, сравнивая записи таблиц Client и Viewing. Мастер создаст запрос на основе предоставленных ему ответов. Прежде чем анализировать результаты выполнения запроса на выборку записей, не имеющих соответствия, рассмотрим его сетку QBE. Ее содержимое показывает, что в таблице Client существует только одна запись, для которой в таблице Viewing нет ни одной связанной записи. Она относится к арендатору с именем 'Mike Ritchie'. Обратите внимание, что флажок Show box поля clientNo в сетке QBE теперь не отмечен, поскольку это поле не требуется в таблице данных.  Запросы с выборкой записей, не имеющих соответствия, являются примером запросов.

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

Для создания запроса с автоподстановкой следует поместить в сетку QBE две таблицы, между которыми существует связь типа "один ко многим", после чего указать поля, которые должны быть помещены в результирующую сетку запроса. Поле соединения должно быть выбрано из таблицы, соответствующей множественной стороне связи. Например, в запросе, содержащем поля таблиц PropertyForRent и Staff, поле staffNo (внешний ключ)следует выбрать из таблицы PropertyForRent. Показана результирующая сетка данных этого запроса, которая позволяет вводить номер вновь добавляемого объекта недвижимости, название улицы и города, в котором он расположен.

Далее можно будет ввести табельный номер работника, который назначается ответственным за этот объект (например,'SA9). Как только это поле будет заполнено, СУБД Microsoft Access автоматически выполнит поиск в таблице Staff и поместит имя и фамилию указанного работника в соответствующие поля формы, в данном случае Mary Howe.

2 глава основной части

2 Изменение содержимого таблиц с помощь активных запросов

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

2.1 Активные запросы создания таблиц

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

Предположим, что требуется создать новую таблицу StaffCut, которая должна содержать столбцы staffNo, fName, IName, position и salary, заполненные данными из существующей таблицы Staff. Прежде всего необходимо подготовить запрос, предназначенный для выбора указанных полей из таблицы staff. Затем в режиме Design View следует изменить тип созданного запроса на Make Table (Создание таблицы...), в результате чего на экран будет выведено диалоговое окно. Это диалоговое окно содержит предложение указать имя и местоположение новой таблицы. После запуска запроса на выполнение СУБД выведет предупреждающее сообщение с предложением указать, следует ли продолжить операцию создания новой таблицы. Если создание таблицы будет продолжено, СУБД создаст новую таблицу с именем StaffCut.

2.2 Активные запросы удаления

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