Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Практическая работа 8,9.
Тема: «Многотабличные запросы»».
Цель работы: научится создавать многотабличные запросы в MS Access и производить Отбор записей в многотабличных запросах. Подведение итогов.
Краткие теоретические сведения:
Часто возникает необходимость в создании запроса, отбирающего информацию из нескольких таблиц или ранее сохраненных запросов. В этом случае списки полей всех используемых таблиц/запросов должны быть включены в окно конструктора создаваемого запроса. Далее рассматриваются различные типы установления связи (объединения) таблиц/запросов, находящихся в окне конструктора, и соответствующие им принципы отбора записей в запросе. Задание связей между таблицами — важный этап конструирования запроса и сделанная на этом этапе ошибка, как правило, приводит к тому, что запрос дает неверный результат. Отметим, что обычно Access устанавливает связи добавленной таблицы с другими таблицами автоматически. Это происходит в следующих случаях:
• таблицы были связаны между собой в окне Схема данных, информация о связи между ними берется из схемы данных;
• связь (внутреннее объединение) устанавливается между таблицами, имеющими поля с одинаковыми именами, если одно из них ключевое.
Однако нередко возникает ситуация, когда приходится либо задавать связь самостоятельно, либо переопределять тип связи, которую установил Access.
Рис. 5.9. Содержимое таблиц А и В
Рассмотрим основные принципы отбора записей, соответствующие различным типам объединения таблиц, на примере двух таблиц А и В.
Каждая из них содержит по одному текстовому полю и две записи (см.рис. 5.9).
Случай несвязанных таблиц
Начнем с рассмотрения ситуации, когда таблицы А и В включены в запрос, но между ними не установлена связь (см. рис. 5.10). В этом случае Access формирует динамический набор данных, записи которого являются декартовым произведением записей исходных таблиц.
Рис. 5.10. Запрос с несвязанными таблицами
Его можно вывести на экран, включив поля обеих таблиц в бланк запроса (см. рис. 5.11).
Рис. 5.11. Результат выполнения Запроса
Декартовым произведением множеств X и Y называется множество Z, состоящее из элементов z=(x,y), где x — элемент множества X, а y — элемент множества Y.
Инструкция SQL, соответствующая этому запросу, имеет вид
SELECT А.a1, В.b1 FROM А, В;
Если включить в бланк запроса поле только одной таблицы, например А (см. рис. 5.12), то результат выполнения запроса будет следующим: итоговый набор содержит записи таблицы А, но они продублированы: их не две, как можно было бы ожидать, а четыре (см. рис. 5.13). Таким образом, сам факт присутствия в окне конструктора посторонней таблицы (В), не связанной с основной таблицей (А), используемой в запросе, приводит к дублированию отбираемых записей.
Появление лишних записей вызвано тем, что Access в этом случае создает такой же динамический набор, как и в предыдущем запросе (декартово произведение таблиц А и В). Однако на этот раз выводятся на экран не оба столбца набора, а лишь первый столбец. Ясно, что число дубликатов записей таблицы А в итоговом наборе равно количеству записей, содержащихся в таблице В.
Инструкция SQL для запроса, представленного на рис. 5.12, имеет вид SELECT А.a1 FROM А, В;
Рис. 5.12. Еще один запрос с несвязанными таблицами
Она отличается от инструкции SQL предыдущего запроса лишь списком выводимых полей.
Рис. 5.13. Результат запроса
Итак, если в окне конструктора находятся две несвязанные таблицы, то Access рассматривает в качестве исходной таблицы их декартово произведение и число обрабатываемых им логических записей равно числу строк первой таблицы, умноженному на число строк второй таблицы. Обычно наличие несвязанных таблиц в окне конструктора — следствие невнимательности пользователя, создающего запрос. Однако можно добавить таблицу в запрос, не связывая ее с другими таблицами, для того чтобы иметь возможность ссылаться на значения ее полей. Например, для получения списка студентов младше студента Иванова можно поступить следующим образом (см. также пример 5.13):
1. Создать и сохранить запрос, дающий дату рождения Иванова.
2. Поместить в окно конструктора таблицу Студенты и сохраненный запрос, не связывая их.
3. Поместить в бланк запроса поля Фамилия, Имя, Дата рождения и в поле Дата рождения задать условие отбора: >[Дата рождения Иванова]![Дата рождения]
4. Выполнить запрос.
Присутствие в окне конструктора запроса Дата рождения Иванова позволяет использовать его значение в условии отбора (см. рис. 5.14).
Рис. 5.14. Использование несвязанных таблиц
Внутреннее объединение таблиц
Чтобы установить связь между таблицами А и В, нужно переместить поле одной таблицы на связываемое с ним поле другой таблицы. Access покажет установленную связь в виде тонкой линии. Включим оба связанных поля в бланк запроса и выполним созданный запрос (см. рис. 5.15). Результирующий набор данных содержит лишь одну запись, причем значения полей совпадают (см. рис. 5.16).
Рис. 5.15. Запрос со связанными таблицами
Мы видим, что при создании связи между таблицами А и В Access объединяет в динамическом наборе записи, имеющие одинаковые значения в связанных полях. Такой тип объединения называется внутренним. Конечно, если запрос содержит какие-либо условия на отбор записей, то в итоговый набор попадут лишь те записи, которые удовлетворяют этим условиям.
Рис. 5.16. Результат запроса со связанными таблицами
Инструкция SQL для созданного нами запроса имеет вид SELECT А.a1, В.b1 FROM А INNER JOIN В ON А.a1 = В.b1; Предложение FROM в ней выглядит не так, как в случае несвязанных таблиц. В нем добавилась операция INNER JOIN (внутреннее объединение), указаны объединяемые таблицы А и В и правило объединения записей А.a1 = В.b1 — совпадение значений в связанных полях.
Итак, для установления связи между таблицами в режиме конструктора запроса нужно переместить с помощью мыши поле из списка полей одной таблицы в соответствующее поле из списка полей другой таблицы. Обычно «перетаскивается» ключевое поле главной таблицы, выделенное жирным шрифтом, на поле внешнего ключа подчиненной таблицы. Связываемые поля должны иметь одинаковый или совместимый тип данных. По умолчанию Access использует внутреннее объединение, при котором из обеих таблиц отбираются лишь записи, содержащие одинаковые значения в полях связи. Если значения в этих полях одинаковы, запрос объединяет две соответствующие записи из обеих таблиц и отображает их как одну запись в динамическом наборе данных. Если для записей одной таблицы нет соответствующих записей из другой таблицы, то динамический набор будет пустым.
Пример 5.14. Нужно узнать, как студенты 9701 группы сдали экзамен по информатике. Фамилии студентов хранятся в таблице Студенты, а их оценки — в таблице Сессия. Поэтому создание запроса нужно начать с включения в окно конструктора этих таблиц. После того как списки полей таблиц появятся в окне конструктора, Access автоматически соединит их линией по полю Код студента, так как связь между этими таблицами типа «один-ко- многим» содержится в схеме данных.
Добавим в бланк запроса поле Группа из таблицы Студенты и поля Оценка и Код экзамена из таблицы Сессия. Для отбора нужных записей введем условие на значения в поле Группа: 9701 и условие на значения в поле Код экзамена: 1 (информатика). Отменим вывод этих полей на экран снятием флажков в строке Вывод на экран.
Для вывода фамилий и имен студентов создадим вычисляемое поле, введя в любую пустую ячейку строки Поле выражение:
Студент: [Студенты]![Фамилия] & " " & [Студенты]![Имя].
Для его создания удобно использовать построитель выражений.
Чтобы список появлялся на экране в отсортированном виде, установим для поля Студент в строке Сортировка значение по возрастанию. На этом создание запроса завершено (см. рис. 5.17).
В том случае, когда две таблицы непосредственно связать нельзя, для их объединения нужно использовать дополнительные таблицы или запросы.
Пример. 5.15. Требуется создать запрос, позволяющий определять, у каких студентов принял экзамен тот или иной преподаватель. Так как таблицы Студенты и Преподаватели не имеют общих полей, то для установления связи между ними нужно добавить в окно конструктора таблицу Сессия, имеющую общие поля с обеими таблицами.
Связь между таблицами Студенты и Сессия устанавливается по полю Код студента, а между таблицами Преподаватели и Сессия — по полю Код преподавателя.
Рис. 5.17. Внутреннее объединение таблиц Студенты и Сессия
Запрос должен выдавать список студентов для любого преподавателя. Поэтому следует использовать запрос с параметром. Параметр лучше задать в поле Код преподавателя, чтобы обеспечить получение правильного результата и в том случае, когда у экзаменатора есть однофамилец. Для этого нужно ввести в этом поле в строке Условие отбора текст в квадратных скобках примерно такого содержания: Введите код преподавателя. В бланк запроса следует также добавить поле Фамилия из таблицы Преподаватели и поля Группа и Фамилия из таблицы Студенты. Так как в запрос включены два поля с одинаковым именем, имеет смысл их переименовать, введя перед старым именем новое имя и отделив имена двоеточием. Затем созданный запрос нужно сохранить. При его запуске Access попросит ввести код преподавателя и после того как это будет сделано, выдаст на экран фамилию преподавателя и список его студентов.
Созданный запрос обычно можно легко изменить. Например, если возникло желание узнать, какие оценки получили студенты, то нужно открыть запрос в режиме конструктора, добавить в бланк поле Оценка и сохранить сделанные изменения. Access позволяет также объединять таблицы по совпадению значений в нескольких полях связи.
Внешнее объединение таблиц
При внутреннем объединении запись таблицы включается в динамический набор лишь в том случае, если в связанной с ней таблице найдется запись, имеющая совпадающее значение в поле связи. Часто возникает необходимость включить в результат запроса и те (а иногда только те) записи таблицы, для которых в таблице, связанной с ней, отсутствуют соответствующие им записи.
Рис. 5.18. Запрос к примеру 5.15
Пусть, например, нужно создать запрос, содержащий результаты сдачи экзамена в группе, причем итоговый список должен включать и фамилии студентов, по каким-то причинам не сдававших экзамен. Если использовать для связи таблиц Студенты и Сессия внутреннее объединение, то в итоговый список попадут лишь студенты, сдававшие экзамен (см. пример 5.14). Поэтому нужно использовать другой тип объединения таблиц. Access позволяет изменить тип объединения таким образом, что из одной таблицы/запроса будут выбраны все записи, независимо от того, содержит ли вторая таблица/запрос соответствующие им записи. Для этого нужно сделать двойной щелчок мышью на линии объединения между таблицами, а в появившемся окне Параметры объединения выбрать подходящий тип объединения и нажать кнопку OK.
Например, если мы хотим выбрать все записи таблицы А и те записи таблицы В, которые имеют совпадающие значения в полях связи, нужно выполнить следующие действия:
1. Создать связь между таблицами А и В путем «перетаскивания» поля а1 на поле в1.
2. Двойным щелчком по линии объединения открыть окно Параметры объединения и установить нужный тип объединения (см. рис. 5.19).
3. Щелкнуть по кнопке ОК и добавить поля а1 и в1 в окно запроса (см. рис. 5.20).
4. Щелкнуть по кнопке Запуск и выполнить запрос (см. рис. 5.21).
Для внешнего объединения Access добавляет стрелку в конце линии объединения. В нашем примере стрелка направлена слева направо от таблицы А к таблице В, а выбранный второй тип объединения называется левым внешним объединением.
Рис. 5.19. Окно Параметры объединения
Если нужно отобрать все записи таблицы В и записи таблицы А, имеющие совпадающие значения в полях связи, то следует выбрать в окне Параметры объединения третий тип, называемый правым внешним объединением. При выборе этого типа стрелка в конце линии объединения будет направлена справа налево от таблицы В к таблице А. Левому внешнему объединению (LEFT JOIN) в нашем примере соответствует инструкция SQL:
SELECT А.a1, В.b1 FROM А LEFT JOIN В ON А.a1 = В.b1;
а правому внешнему объединению (RIGHT JOIN) — инструкция SQL:
SELECT А.a1, В.b1 FROM А RIGHT JOIN В ON А.a1 = В.b1;
Отметим, что любой из описанных выше типов объединения таблиц может быть задан в окне Связи (см. рис. 2.6 на стр. 33) при определении связей между таблицами. Для этого следует щелкнуть по кнопке Объединение. Установленный тип связи будет использован по умолчанию Access при объединении таблиц в окне конструктора запросов.
Рис. 5.20. Запрос с левым внешним объединением
Рис. 5.21. Его результат
Пример 5.16. Нужно создать запрос Адреса студентов, содержащий список студентов курса, причем для проживающих в общежитии должен быть указан их адрес: номер общежития и номер комнаты. Для этого следует добавить в окно конструктора две таблицы: Студентыи Общежитие, содержащие нужную информацию. Так как поле Код студента является ключевым в таблице Студенты, между ними будет автоматически установлена связь по этому полю, имеющая внутренний тип объединения. Если оставить эту связь без изменения, то Access отберет лишь записи с информацией о студентах, живущих в общежитии. Нам же нужен полный список студентов, поэтому этот тип объединения следует изменить на тип объединения, отбирающий все записи из таблицы Студенты и соответствующие им записи из таблицы Общежитие. Затем нужно включить в бланк запроса поля с нужной информацией и сохранить в БД. После выполнения запроса появится список всех студентов курса с их адресами (см. рис. 5.22). Для студентов, не живущих в общежитии, поля Общежитие и Адрес остаются пустыми (содержат значение Null).
Рис. 5.22. Запрос Адреса студентов
Использование мастера Записи без подчиненных
Созданный запрос можно легко превратить в запрос, который находит студентов, не живущих в общежитии. Для этого нужно лишь добавить в качестве условия отбора в поле Комната или Общежитие выражение Is Null. Для создания такого рода запросов можно также использовать мастера Записи без подчиненных.
Пример 5.17. Создадим с помощью этого мастера запрос, который находит студентов, не сдававших сессию.
Рис. 5.23. Запрос, созданный мастером Записи без подчиненных
Таким студентам в таблице Студенты соответствуют записи, не имеющие связанных с ними записей в подчиненной таблице Сессия. После вызова мастера путем выбора соответствующего пункта в окне Новый запрос нужно последовательно ответить на следующие вопросы (ответ набран курсивом):
1. Указать таблицу, не имеющую связанных записей с таблицей, указываемой в следующем пункте: Студенты.
2. Указать таблицу с подчиненными записями: Сессия.
3. Указать поля, используемые для связи таблиц: в обеих таблицах Код студента.
4. Выбрать поля, отображаемые в результате выполнения запроса:
Группа, Фамилия, Имя.
5. Задать имя запроса: Студенты, не сдававшие сессию.
В результате выполнения созданного мастером запроса (см. рис. 5.23) на экране появятся сведения о студентах, не сдававших сессию (рис. 5.24).
Рис. 5.24. Студенты, не сдававшие сессию
Самообъединение
Иногда для получения нужной информации следует объединить таблицу саму с собой, создавая тем самым самообъединение таблицы. Такая ситуация, в частности, возникает, если в таблице есть поле, которое ссылается на другое поле той же таблицы.
Рис. 5.25. Таблица Служащие
Пример 5.18. Пусть имеется таблица Служащие, содержащая поле Подчиняется с идентификационными номерами начальников, причем сведения о них хранятся в той же таблице.
Если нужно, чтобы вместо номера стояла фамилия начальника, этого можно добиться, создав запрос, использующий копию таблицы Служащие (см. рис. 5.26).
Для создания этого запроса нужно включить в окно конструктора таблицу Служащие дважды. Второй раз она будет добавлена под именем Служащие-1, которое во избежание ошибок лучше заменить другим. Для этого нужно щелкнуть по копии таблицы правой кнопкой мыши, выбрать пункт Свойства, а затем в поле Псевдоним ввести текст Служащие_копия. Затем следует установить связь между таблицами, соединив поле Подчиняется таблицы Служащие с полем Код ее копии В качестве типа соединения лучше выбрать левое внешнее объединение, так как при внутреннем объединении в результат запроса не будет включена информация о самом главном начальнике. После этого следует включить в бланк запроса поле Сотрудник из обеих таблиц, изменив название этого поля из таблицы Служащие_копия на Подчиняется. Результат запроса представлен на рис. 5.27 (Чтобы в пустом поле напротив фамилии Смирнов появился текст, достаточно задать в свойствах поля Подчиняется такой формат: @;"главный начальник".)
Рис. 5.27. Структура подчиненности
Самообъединение можно использовать и в других случаях.
Пример 5.19. Пусть нужно найти студентов, которые учатся в той же группе, что и студент Иванов (код 891294), и являются его тезками. Одним из возможных путей решения этой задачи является создание запроса, использующего самообъединение таблицы Студенты (см. рис. 5.28) (Можно также использовать функцию DLookUp.)
Рис. 5.28. Еще один пример на самообъединение
В этом запросе Access объединяет пары записей, имеющие одинаковые значения в полях Группа и Имя. После выполнения запроса в динамическом наборе остаются лишь три записи (см. рис. 5.29)
Рис. 5.29. Тезки Иванова
Объединение таблиц по отношению неравенства
В большинстве случаев таблицы объединяются по условию равенства значений в полях связи, но допустимо объединение таблиц и по условию «неравенства». В этом случае условием связи может быть любой из операторов сравнения: >, >=, <, <=, <>.
Для создания запроса, использующего такие условия связи, можно поступить следующим образом:
1. Создать в окне конструктора связь типа равенства.
2. Перейти в режим SQL и заменить знак "=" другим оператором сравнения. Однако такой запрос уже нельзя представить в режиме конструктора.
Пример 5.20. Заменив знак "=" на "<>", в приведенной на стр. 93 инструкции SQL, можно создать запрос, объединяющий записи таблиц А и В с несовпадающими значениями в связанных полях. Его результат виден на рис. 5.30.
Рис. 5.30. Связь по неравенству
Соответствующая инструкция SQL имеет вид:
SELECT А.a1, В.b1 FROM А INNER JOIN В ON А.a1 <> В.b1;
Можно поступить иначе: не связывать таблицы в окне конструктора, а записать условие на несовпадение значений полей в строке условий. В нашем примере это условие накладывается на значения в поле a1 и выглядит так: <> [b1].
Соответствующая инструкция SQL имеет вид SELECT А.a1, В.b1 FROM А, В WHERE (((А.a1)<>[b1]));
Отбор записей, формирующих группы
В том случае, когда из групп должны быть исключены некоторые записи, нужно добавить в бланк запроса поле или поля, которые будут использованы для отбора нужных записей. Для создания условия отбора по данному полю нужно выбрать в строке Групповая операция значение Условие и ввести условие в строку Условие отбора. Access автоматически отключит вывод на экран значений этого поля.
Пример 5.23. Определить число студентов в каждой учебной группе, родившихся в 1973 году или позднее. Для этого нужно в бланк запроса включить дополнительно поле День рождения, выбрать в строке Групповая операция значение Условие и ввести условие отбора > 31.12.72. Access добавит к дате с двух сторон # и бланк запроса будет иметь вид, представленный на рис. 5.34.
Рис 5.34. Отбор записей в группах
После добавления этого условия в Access включит в формируемые группы лишь те записи, которые относятся к студентам, родившимся позднее 31 декабря 1972 года.
Пример 5.24. Определить средние баллы по информатике девушек в каждой учебной группе. Этот запрос использует данные из двух таблиц: Студенты и Сессия. В его бланк следует включить следующие поля (см. рис. 5.35):
Группа — используется для группировки записей;
Пол и Код экзамена — служит для отбора нужных записей в группах;
Оценка — по этому полю производится групповая операция
Avg (вычисление среднего значения среди оценок по информатике). Результат выполнения запроса представлен на рис. 5.36.
Рис 5.36. Итоговый результат
Чтобы получить значения средних баллов в таком (отформатированном) виде необходимо настроить свойства итогового поля Оценка. Для этого нужно щелкнуть правой кнопкой мыши по полю Оценка, открыть список его свойств и установить для свойства Формат поля значение Фиксированный, для свойства Число десятичных знаков — 2, а для свойства Подпись — Средний балл (см. рис. 5.37).
Рис 5.37. Установка свойств поля Оценка
Отбор групп
В итоговый динамический набор можно включать не все группы. Для того чтобы исключить ненужные группы, следует ввести условия отбора в любое поле, содержащее в строке Групповая операция значение Группировка или одну из статистических функций.
Пример 5.25. Определить группы, в которых учится более 16 студентов.
Для этого достаточно добавить в поле Число студентов бланка запроса из примера 5.21 (см. рис. 5.31) условие отбора: > 16. В результате выполнения этого запроса будут найдены номера учебных групп.
Рис. 5.38. Использование функции Count(*)
Для подсчета числа студентов в учебной группе можно также использовать функцию Count(*). В этом случае бланк запроса будет иметь вид, представленный на рис. 5. 38. Строка условий может содержать выражение, в состав которого входит результат некоторой статистической операции. Для вычисления этого результата должна использоваться не обычная статистическая функция, а статистическая функция по подмножеству. более высокий средний балл, чем на курсе в целом. Запрос использует информацию из таблиц Студенты (номера групп) и Сессия (оценки). В бланк итогового запроса следует включить следующие поля (см. рис. 5.39):
Рис. 5.39. Использование функции Davg в условии отбора
Группа — используется для группировки записей;
Оценка — используется как итоговое поле для подсчета средних оценок в группах, а также для отбора нужных групп записей;
Код экзамена — это поле служит для отбора записей в группах (код экзамена по информатике равен 1).
Условие отбора групп выглядит так:
>DAvg("[Оценка]";"Сессия";"[Код экзамена]='1'")
Результат вычисления значения функции DAvg — средний балл по информатике на курсе. Для нахождения среднего балла на курсе можно также использовать подчиненный запрос. В этом случае условие отбора в поле Оценка будет таким:
>(SELECT DISTINCTROW Avg(Сессия.Оценка) AS Avg_Оценка
FROM Сессия WHERE (((Сессия.[Код экзамена])="1"));)
Пример 5.27. Определить общую сумму стипендии, полученной студентами групп 9701-9703 в сентябре, октябре и за оба месяца. Так как информация о распределении студентов по группам содержится в таблице Студенты, а информация о стипендии — в таблице Стипендия, то создание запроса начнем с включения этих таблиц в окно конструктора. Они связываются по полю Код студента. Затем нужно щелкнуть по кнопке Групповые операции и включить в бланк запроса поле Группа, которое будет использоваться для группировки записей. Для отбора нужных групп следует ввести условие < 9704 или другое равносильное ему условие, например, 9701 Or 9702 Or 9703. Следующий шаг — создание итоговых полей, подсчитывающих суммарную стипендию за каждый месяц. Для этого нужно включить соответствующие поля в бланк запроса, выбрать в качестве групповой функции Sum и заменить названия, которое Access дает итоговым полям (Sum_<месяц>) на более подходящие.
На завершающем этапе построения запроса добавим итоговое поле, подсчитывающее суммарную стипендию за оба месяца. Для этого создадим вычисляемое поле, введя в пустую ячейку строки Поле выражение Nz([Стипендия]![Сентябрь])+Nz([Стипендия]![Октябрь])1
и выберем в качестве групповой функции Sum. Это поле тоже нужно переименовать и, вызвав его свойства, установить ему в качестве формата вывода значений Денежный. Резуль таты запроса представлены на рис. 5.40.
Рис. 5.40. Стипендия за два месяца
Интересно, что Access модифицировал созданный нами запрос, внеся следующие изменения в итоговое поле Всего (см. рис. 5.41):
Рис 5.41. Отбор групп в итоговом запросе
выражение, которое вычисляется в этом поле, заменено выражением Sum(Nz([Стипендия]![Сентябрь])+Nz([Стипендия]![Октябрь]))
в строке Групповая операция групповая функция Sum заменена групповой операцией Выражение2.
1 О том, как это сделать с помощью построителя выражений, и почему использована функция Nz, см. пример 4.1 и замечание к нему.
2 Эта групповая операция обычно выбирается, если в выражении в строке Поле используются одна или несколько групповых функций.
В запрос можно включить дополнительные итоговые поля. Например, для подсчета числа студентов, получающих стипендию в сентябре, нужно добавить в бланк поле Сентябрь и выбрать групповую функцию Count.
Ход работы:
Вывод:…