Самоучитель Microsoft Access 2013

Глава 4 
КАЛЕНДАРЬ. Пусть в таблице ПОСТАВКА_ПЛАН хранится пять строк, пред-
ставляющих план поставок одного изделия на три месяца, а в таблице 
КАЛЕНДАРЬ — три строки, представляющие три месяца (рис. 4.30). 
Рис. 4.29. 
Запрос для представления плана поставок изделий нарастающим итогом
в режиме конструктора 
Рис. 4.30. 
Таблицы с данными о плане поставок товаров и календарь 
"Декартово произведение" этих таблиц представлено на рис. 4.31 и объединяет 
каждую строку плана с каждым месяцем календаря. Там же показан результат 
выполнения операции отбора записей, содержащий только 11 строк. В соответ-
ствии с заданным условием отбора из "декартова произведения" остаются толь-
ко те строки, в которых срок поставки меньше или равен номеру месяца из ка-
лендаря.
В результате группировки по коду товара и номеру месяца из календаря форми-
руется три группы по числу месяцев в календаре. Таким образом, для товара бу-
дет получено столько строк, сколько месяцев включает календарь, а суммарное 
количество будет равно, запланированному к выпуску на конец каждого месяца 
(рис. 4.32). 

---

Запросы 
187 
Рис. 4.31. 
"Декартово произведение" таблиц ПОСТАВКА_ПЛАН и КАЛЕНДАРЬ
до и после отбора записей в соответствии с условиями
Рис. 4.32. 
План поставок товара нарастающим итогом 
О
БРАТИТЕ ВНИМАНИЕ
При выполнении запроса сначала производится объединение записей и их отбор
в соответствии с заданными условиями и только затем группировка полученных
записей. 
9.
Просмотрите запрос в режиме SQL. Инструкция 
SELECT
для этого запроса имеет 
вид: 
SELECT ПОСТАВКА_ПЛАН.КОД_ТОВ, КАЛЕНДАРЬ.[номер месяца],
Sum(ПОСТАВКА_ПЛАН.КОЛ_ПОСТ) AS [Sum-КОЛ_ПОСТ] 
FROM ПОСТАВКА_ПЛАН, КАЛЕНДАРЬ 
WHERE (((ПОСТАВКА_ПЛАН.СРОК_ПОСТ)<=[номер месяца])) 
GROUP BY ПОСТАВКА_ПЛАН.КОД_ТОВ, КАЛЕНДАРЬ.[номер месяца]; 
В инструкции 
SELECT
в предложении 
FROM ПОСТАВКА_ПЛАН, КАЛЕНДАРЬ 
имена таб-
лиц разделены запятой, что и определяет операцию "декартово произведение" 
для объединения записей таблиц. Предложение 
WHERE ПОСТАВКА_ПЛАН.СРОК_ПОСТ<= 
[номер месяца] 
определяет отбор нужных записей. И наконец, предложение 
GROUP BY
определяет поля группировки. Суммирование количества для группы 
выполняется соответствующей функцией, записанной в списке полей инструк-
ции. 

---

188 
Глава 4 
Тот же результат может быть получен, если в инструкции SQL заменить приве-
денные в примере предложения 
FROM
и 
WHERE 
одним предложением:
FROM ПОСТАВКА_ПЛАН INNER JOIN КАЛЕНДАРЬ ON 
ПОСТАВКА_ПЛАН.СРОК_ПОСТ<=КАЛЕНДАРЬ.[номер месяца]
Однако такая инструкция не может быть представлена в режиме конструктора 
Access, т. к. выражение объединения использует оператор <=, который не под-
держивается в режиме конструктора. В 
ON
в качестве оператора сравнения полей 
связи можно использовать только знак =.
Решение задачи,
требующей выполнения нескольких запросов 
Чтобы решить сложную задачу, чаще всего необходимо разбить ее на несколько 
подзадач, алгоритм каждой из которых может быть реализован выполнением одно-
го достаточно простого запроса. Для каждого из запросов определяются входные и 
выходные данные. В простейшем случае выходные данные предшествующего за-
проса являются входными для следующего, построенного на нем запроса. При 
этом, выполнив только последний запрос в цепочке построенных друг на друге за-
просов, вы инициируете последовательное выполнение всех запросов цепочки и 
полное решение задачи. Для представления алгоритмов решения задач целесооб-
разно использовать функционально-технологическую схему, на которой приводит-
ся цепочка всех запросов с описанием и входными и выходными данными.
Задача 
Проанализируйте выполнение общего плана поставок каждого из товаров на конец 
заданного месяца. При решении этой задачи подсчитайте количество товара, запла-
нированного к поставке и отгруженного покупателям, и получите величину недо-
поставки товаров на конец заданного месяца.
1.
Данные о плановых поставках хранятся в таблице ПОСТАВКА_ПЛАН. На ее 
основе создайте запрос для подсчета суммарного количества каждого из това-
ров, запланированных к поставке на конец заданного месяца (рис. 4.33).
2.
Сохраните запрос под именем 
План
. 
В запросе 
План
производится группировка записей таблицы по полю 
Код това-
ра
. Для операции используются только записи, в которых срок поставки (месяц) 
имеет значение меньшее или равное заданному параметром запроса с именем 
Номер месяца
. В каждой группе записей о поставках одного товара суммиру-
ются количества, запланированные к поставке. 
3.
Данные о фактически поставленных, отгруженных покупателям товарах, хра-
нятся в таблице ОТГРУЗКА. На ее основе создайте запрос для подсчета суммар-
ного количества отгруженных товаров. Для отбора поставок, выполненных до 
конца заданного месяца, в запрос добавьте таблицу НАКЛАДНАЯ, в которой 
хранится дата отгрузки товаров (рис. 4.34).

---

Запросы 
189 
Рис. 4.33. 
Подсчет суммарного количества товаров, запланированных к поставке 
Рис. 4.34. 
Подсчет суммарного количества отгруженных товаров 
4.
Сохраните запрос под именем 
Факт
. 
Таблицы, на которых построен запрос 
Факт
, находятся в отношении "один-ко-
многим". Для их связи по составному ключу установлены параметры обеспече-
ния целостности. В результате объединения этих таблиц первым способом фор-
мируется таблица запроса с числом записей, равным числу записей в подчинен-

---

190 
Глава 4 
ной таблице ОТГРУЗКА. Причем каждая запись об отгрузках товара дополняет-
ся датой из связанной записи главной таблицы НАКЛАДНАЯ.
5.
Для отбора только тех накладных, по которым отгружался товар до конца задан-
ного месяца, из даты отгрузки с помощью функции 
Month
выделяется номер ме-
сяца и для этого вычисляемого поля в условие отбора вводится параметр запроса 
с именем 
Номер месяца
, совпадающим с именем параметра в предыдущем за-
просе. 
6.
Для сравнения количества запланированного к поставке и отгруженного создай-
те новый запрос. Добавьте в него два предыдущих запроса: 
План
и 
Факт
.
7.
Поскольку некоторые из запланированных товаров могли не отгружаться и в то 
же время могла производиться отгрузка товаров, которые не были запланирова-
ны, добавьте в запрос таблицу ТОВАР, в которой представлена вся номенклату-
ра товаров фирмы. Свяжите эту таблицу с каждым из запросов по полю 
КОД_ТОВ
и укажите на объединение ее записей с записями таблиц запросов План и Факт 
вторым способом (рис. 4.35). Такое объединение таблиц приведет к включению 
в результат и тех записей о товарах:
•
по которым был определен план поставки, но эти товары не отгружались
вовсе; 
•
которые отгружались несмотря на то, что не были запланированы к поставке;
•
которые вообще не планировались к поставке и не отгружались. 
Рис. 4.35. 
Вычисление разности плановых и фактических поставок товаров
8.
Обратите внимание, что при формировании записи вторым способом объедине-
ния в таблицу запроса включаются и те записи из левой таблицы (ТОВАР), для 
которых нет связанных записей в правой таблице (например, План). При этом

---

Запросы 
191 
в формируемой записи поля выбираемые из правой таблицы заполняются зна-
чением 
Null
. 
9.
Учитывая, что во всех пустых полях таблицы запроса определено значение 
Null
, для получения разности между запланированным и фактически 
поставленным количеством товара создайте вычисляемое поле с выражением 
Nz([Sum–КОЛ_ПОСТ])–Nz([Sum–КОЛ_ОТГР])
. Присвойте вычисляемому полю имя 
Отклонение
. Функция 
Nz
возвращает существующее в поле значение или — 
для поля со значением 
Null
— новое значение, указанное вторым аргументом. 
Если второй аргумент не указан, по умолчанию для числового поля со 
значением 
Null
возвращается 
0
, а для символьного — пустая строка. 
10.
Результатом арифметических операций с полем 
Null
является 
Null
. Убедитесь 
в этом, исключив из выражения функцию 
Nz
.
11.
Сохраните запрос под именем 
Анализ выполнения плана
. 
12.
Выполните запрос. В таблице запроса 
Анализ выполнения плана
(рис. 4.36) 
представлен весь список товаров фирмы. Показано, что не все товары были за-
казаны в договорах, по большинству заказанных товаров выполнялась отгрузка, 
а один товар был отгружен без предварительного оформления договора.
О
БРАТИТЕ ВНИМАНИЕ
Выполнение запроса 
Анализ выполнения плана
инициирует выполнение запросов 
План
и 
Факт
, и нет необходимости в их предварительном выполнении. 
Рис. 4.36. 
Результат вычисления разности плановых и фактических поставок товаров
13.
Запишите в условие отбора поля 
Sum–КОЛ_ПОСТ
значение 
Not Null
. Отобразятся 
только те строки, которые относятся к товарам, на которые были заключены 
договоры. 
14.
Для того чтобы явно увидеть, в какой последовательности производится объ- 
единение таблиц запроса, просмотрите запрос в режиме SQL. Инструкция 
SELECT
для этого запроса имеет вид: 

---

192