2.2 Запросы, которые отбирают данные из нескольких таблиц

При создании таких запросов к верхней части окна конструктора запросов выбираем несколько таблиц. Если связи между таблицами были созданы в окне “Схема данных”, то в этом окне они появятся автоматически. Теперь можно выбирать в один запрос поля из нескольких таблиц. Существует два разных типа объединения двух таблиц – внутреннее и внешнее. Если объединение внутреннее (именно его вы установили), то в один запрос будут объединены записи, в которых совпадают значения в полях связи. Если какая-нибудь запись в одной таблице не имеет соответствующей записи в другой таблице, то эта запись не будет включена в запрос. Внешнее объединение будет рассмотрено позднее.

Создание полей, которые вычисляются. В таблицах баз данных (в отличие от электронных таблиц) никогда не сохраняют данные, которые могут быть вычислены по данным из таблиц. В большинстве случаев потому, что это требует значительных затрат памяти и замедляет поиск необходимых данных и обработку данных в таблицах. Все необходимые вычисления выполняют в запросах, создавая поля, которые вычисляются. Для создания таких полей в свободном столбце конструктора запросов записывают новое имя поля, знак “:”, а потом выражение, которое использует имена других полей и вычисляет необходимое значение. Например, вычислим стоимость заказа, если есть поля Стоимость единицы и Количество единиц. В новом столбце запишем:

Стоимость заказа: [Стоимость единицы]*[Количество единиц]

Имена полей записываются в квадратных скобках.

При записи выражений можно использовать встроенные функции Access. Имена этих функций, в отличие от Excel, не переведены на русский, они пишутся по-английски, но по смыслу многие из них совпадают с функциями Excel. Приведем несколько функций:

Iif(условие; выражение1; выражение2) – вычисляет выражение1, если условие выполняется, и выражение2, если условие не выполняется (совпадает с функцией ЕСЛИ в Excel).

DateDiff(“d”; дата1; дата2) - вычисляет разность между двумя датами в днях. Если первый аргумент “m” – разность дат вычисляется в месяцах, если “y” - в годах.

Полный список всех функций вы найдете в окне «Построитель выражений».

2.2.1 Создадим запрос, который отбирает и объединяет записи из двух таблиц - Книги и ЧитКниги, чтобы показать, какие книги были выданы читателям. Создадим вычисляемое поле с именем Пеня, которая начисляется на каждую книгу, которая не возвращается своевременно. Размер пени равен 1% от стоимости книги за каждый просроченный день. Создадим запрос с именем Список1. Для создания запроса Список1 перейдите на вкладку Запросы и выберите кнопку Создать. Добавьте в запрос таблицы Книги и ЧитКниги. В окне схемы данных должна быть показана связь между таблицами по полю Инв№ - инвентарный номер книги. Перетяните мышью в нижнюю половину окна поля, которые необходимо включить в запрос: Автор, Название, Стоимость, Инв№, Дата выдачи, Дата возврата, NB. Последнее поле потребуется для связи запроса с таблицей Читатели. В первой свободной колонке нижней части окна создайте поле, которое вычисляется, с именем Пеня. Для этого наберите в верхней строке (где располагается имя поля) такой текст:

Пеня: iif([Дата возврата]>Date(); DateDiff(“d”; [Дата возврата]; Date())*0,01*[Стоимость]; 0).

Для создания такого выражения можно использовать Построитель выражений. Для этого нажмите кнопку Построить на панели инструментов. На экране открывается окно Построителя выражений. В этом окне создается выражение последовательным добавлением к нему полей таблиц или запросов, функций, констант и операторов (рисунок 13). Имена полей записываются в квадратных скобках.

Рисунок 13. Использование Построителя выражений

Сохраните запрос под именем Список1. Просмотрите запрос, щелкнув на кнопке Открыть. Размер пени, разумеется, зависит от текущей даты, на которую он вычислялся. Пример приведен на 26.01.03.

Рисунок 14. Результат выполнения запроса Список1

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

Для этого выберите таблицы Книги, Читатели и запрос Список1. В бланк запроса включите поля Фамилия из таблицы Читатели, Инв№ из таблицы Книги, Стоимость из таблицы Книги и Пеня из запроса Список1. Для создания итогового запроса на панели инструментов выбираем пиктограмму Групповые операции. В бланке запроса появляется строка Групповые операции. Для первого поля запроса Фамилия из таблицы Читатели выбираем Группировка, для Стоимость из таблицы Книги – SUM (подсчет суммы), для поля Инв№ из таблицы Книги- Count и для Пеня из запроса Список1- SUM. Для того, чтобы при просмотре запроса каждое поле имело понятную подпись, определим свойства полей. Для этого выбираем ВИД-Свойства и задаем Подпись для каждого поля –Количество книг, Общая стоимость книг и Общая сумма пени.

Рисунок 15. Конструктор запрос 2.2

Рисунок 16. Результат выполнения запроса 2.2

2.2.3 Создание запроса на поиск записей, которые не имеют связанных в другом списке. Такие запросы называются «Записи без подчиненных». Для создания таких запросов необходимо изменять тип объединения таблиц на внешний. При таком типе объединения в запрос обязательно включаются все записи из первой таблицы и к ним прибавляются поля из второй таблицы, если во второй таблице есть запись, для которой значения связанных полей совпадают. Если во второй таблице такой записи нет, то к записи из первой таблицы прибавляются поля с пустыми значениями. Внешнее объединение в конструкторе таблиц изображается линией со стрелкой.

Например, создадим список книг, которые не выданы на руки читателям. В запрос включим две таблицы Книги и ЧитКниги. Включим в запрос поля Инв№, Шифр, Автор, Название из таблицы Книги и Инв№ из таблицы ЧитКниги. Найдем те записи, которые присутствуют в таблице Книги и отсутствуют в таблице ЧитКниги. Изменим Параметры объединения. Необходимо создать Внешнее объединение. Для этого дважды нажмите на линии связи между таблицами. В окне диалога выберите второй тип – “Объединение ВСЕХ записей из ‘Книги' и только тех записей из 'ЧитКниги', в которых связанные поля совпадают”. Теперь создано внешнее объединение, в которое включены все книги из таблицы Книги. Для тех книг, которые не имеют записей из таблицы ЧитКниги устанавливается значение ‘Null’, поэтому для поля Инв№ из таблицы ЧитКниги установим такие параметры Условие отбора – Is Null и Вывод на экран отсутствует.

Рисунок 17. Конструктор запроса 2.3.

Убедитесь, что запрос выбирает необходимую информацию.

Рисунок 18. Установка параметров объединения

Рисунок 19. Результат выполнения запроса 2.2