Запросы с подгруппировкой

Запросы к связанным таблицам

Сложные запросы

Если в условиях отбора используется несколько полей, то они мо-гут соединяться оператором “И” либо “ИЛИ”. Если аргументы поиска записаны в одной строке, то считается, что они соединены оператором “И” (“AND”). Если аргументы поиска записаны в разных строках, то считается, что они соединены оператором “ИЛИ” (“OR”).

Рис. 2.9. Сложный запрос (оператор AND)

На рис. 2.9, 2.10 изображены примеры таких запросов. Первый из них выдает список военнообязанных мужчин (запрос “И”; аргументы запроса расположены на одной строке), второй (запрос “ИЛИ”; аргумен-ты запроса расположены на разных строках) – всех мужчин и военно-обязанных женщин.

Рис. 2.10 Сложный запрос (оператор OR)

Если была предварительно определена схема данных, то при до-бавлении таблиц в запрос они будут должным образом связаны. Даже если связи между таблицами не были созданы пользователем предвари-тельно, то при добавлении в запрос двух таблиц, содержащих поля с одинаковым или совместимым типом данных, а также, если одно из по-лей связи является ключевым, связи могут быть созданы автоматически. Автоматическое объединение (соединение) можно разрешить или запре-тить. Для этого надо выполнить следующую последовательность шагов:

1. В меню “Сервис” выбрать команду “Параметры”

2. Перейти к вкладке “Таблицы/Запросы”.

3. Установить/снять флажок “Автоматическое объединение”.

Параметр “Автоматическое объединение” относится только к но-вым запросам.

Если связи не были определены предварительно, и связи не созда-ны автоматически, то надо задать соединение таблиц вручную (так же, как это делалось при задании схемы).

Внимание!!! Если связь не задана (и не отменено “Автоматическое объединение”), то будет осуществляться связь каждой записи одной таблицы с каждой записью второй таблицы.

Рис. 2.11 Выполнение запросов на связанных таблицах

Надо осторожно относиться к формированию запросов к связан-ным таблицам. Как Вы думаете, что будет получено в ответ на запрос, изображенный на рис. 2.11? На самом деле ответить на этот вопрос, не имея дополнительной информации, нельзя. Чтобы ответить на постав-ленный вопрос надо знать, каковы параметры объединения (если Вы внимательны, то по виду линии сможете определить вид связи; сравните рис. 2.11 и 2.12) и какие значения имеют свойства «Уникальные запи-си» и «Уникальные значения» (этого на схеме не видно). Если задано обычное («внутреннее») соединение таблиц и для свойства «Уникальные значения» задано значение «Да», то в ответ на запрос, содержащий в бланке запроса поле «ФИО» и больше ничего, будет получен список со-трудников, имеющих детей.

Рис. 2.12 Выполнение запросов на связанных таблицах (вариант 2)

Совет:

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

2. При проектировании структуры базы данных тщательно продумы-вайте имена, которые вы даете полям разных таблиц.

3. Проверяйте связи, которые система задает автоматически.

Существуют понятия внутреннего, левого и правого соединения. В QBE Access это задается не в бланке запроса, а при задании схемы или при определении параметров связи в окне запроса. При формулировании запроса надо уточнить, какой тип объединения4 был задан, и, при необ-ходимости, изменить тип соединения, на тот, который необходим имен-но для этого запроса, так как тип объединения будет влиять на правиль-ность ответа. Так, например, если необходимо выдать список всех со-трудников, а для тех, кто имеет детей – информацию о детях, то для со-единения таблиц “СОТРУДНИК” и “ДЕТИ” надо выбрать вторую аль-тернативу в окне “ Параметры объединения ”.

4 В реляционной теории различают операции “соединения” и “объединения”. То, о чем идет сейчас речь, является реляционной операцией соединения. Но в системе Access обе эти операции называются “объединением”, и когда рассматриваем, как это сделать в Access, то приходится переходить на терминологию этой системы.

Изменить тип объединения в запросе можно, выделив нужную связь и нажав на правую кнопку мыши. В появившемся контекстном ме-ню (рис. 2.13) выбрать «Параметры объединения». Либо выбрать по-зицию меню «Вид/Параметры объединения».

Рис. 2.13. Выбор типа объединения таблиц

Возможно создание запросов, в котором таблица соединяется сама с собой (так называемое «Самообъединение»). Например, для класса объектов «СОТРУДНИК» имеется связь «Быть руководителем». В рас-сматриваемом нами примере для отражения этой связи в таблицу «СО-ТРУДНИК» введено поле «Руководитель», которое содержит код со-трудника, являющего руководителем данного сотрудника.

Рис. 2.14. Пример использования самообъединения таблиц в запросе

Для того чтобы объединить две копии одной и той же таблицы в запросе надо в режиме конструктора запроса дважды добавить эту таб-лицу в запрос. Далее надо осуществить соединение таблицы с ее копией обычным путем (установить курсор на поле связи в пером экземпляре таблицы и, не отпуская кнопки мыши, переместить появившийся значок на соответствующее поле в списке полей другой таблицы). На рис. 2.14 изображен запрос «Для каждого из руководителей выдать список его подчиненных». Для того чтобы в результатной таблице было понятно, что означает поле «ФИО» в каждом столбце, можно переименовать эти столбцы, назвав первый «Руководитель», второй – «Подчиненный». Для этого можно щелкнуть правой клавишей мыши на соответствующем по-ле, в высветившемся меню выбрать позицию «Свойства» и в появив-шемся окне «Свойства поля» в строке “ Подпись ” ввести требуемый за-головок столбца (рис. 2.15).

Рис. 2.15. Изменение подписи поля

Вид результатной таблицы после произведенных действий пред-ставлен на (рис. 2.16).

Рис. 2.16. Вид результатной таблицы (результат запроса с самообъе-динением таблицы)

Термина «обобщенный» или «агрегирующий» оператор в Access нет. Есть просто понятие «встроенные функции Microsoft Access», а сре-ди них – «статистические функции» и «статистические функции по под-множеству».

Статистические функции – это: Sum (сумма), Count (количество записей, возвращаемых запросом), Avg (среднее), Var (дисперсия) и др., используемые для расчета итоговых значений. Статистическая функция, с помощью которой в запросе обрабатываются значения поля, может быть выбрана в ячейке строки "Групповая операция" в бланке запроса. Первоначально эта строка в бланке запроса отсутствует. Чтобы она поя-вилась, надо выбрать позицию “ Групповые операции ” меню “ Вид ”, или нажать кнопку со знаком “У” на панели инструментов.

Рис. 2. 17. Использование групповых операций в запросах. Выбор агреги-рующей функции

Первым полем, выводимым в ответ, должно быть поле, по которо-му производится группировка, а затем – поля, над которыми произво-дятся вычисления. Все групповые операции, кроме Count, могут выпол-няться только над числовыми полями.

В строке «Групповая операция» щелчком мыши можно открыть список доступных функций, в котором можно осуществить выбор нуж-ной статистической функций для выполняемых над полем вычислений.

На рис. 2.17 приведен пример использования групповых операций в запросе (Count – подсчет числа сотрудников, работающих на каждой кафедре).

Рис. 2.18. Запрос с вычисляемым полем, используемым в условии отбора. Групповые операции

В Access предварительно упорядочивать таблицу по полю, по ко-торому ведется группировка, не обязательно.

Выражения, определяющие вычисляемые поля, создаются с по-мощью мастера простых запросов или вводятся пользователем в строку «Групповая операция» бланка запроса. В бланке запроса задают также условия отбора, с помощью которых определяются группы, для которых вычисляются итоговые значения, записи, включаемые в вычисления, или результаты, отображаемые после выполнения расчетов. На рис. 2.18 изображен запрос, в котором условия отбора применены к вычисляемо-му полю («Выдать список кафедр, на которых работает меньше 3 чело-век»).

Если предположить (а это практически всегда так), что нет ка-федр, на которых не работает ни одного человека, то результат за-проса будет верен и когда задано внутреннее соединение, и если задано

левое соединение. Но, предположим, что задается аналогичный по су-ществу запрос «Выдать список сотрудников, имеющих меньше двух детей» на двух связанных таблицах «СОТРУДНИК» и «ДЕТИ». Всегда есть вероятность, что имеются сотрудники, которые не имеют де-тей. В случае если будет использовано внутреннее соединение (а оно за-дается по умолчанию), то такие сотрудники не попадут в ответ (т. е. результат ответа будет не соответствовать действительности). На результат запроса «Выдать список сотрудников, имеющих больше двух детей» параметры объединения таблиц не окажут влияния.

Резюме:

1. При реализации запросов на связанных таблицах по ER-модели уточните характер связи между соответ-ствующими объектами (это может повлиять на форму-лировку запроса).

2. Определите, какие должны быть заданы параметры объединения для данного запроса (в случае необхо-димости измените эти параметры).

3. Будьте внимательны при формулировании запроса: даже на первый взгляд однотипные запросы требуют разной реализации.

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

Дата добавления: 2017-01-14