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

Глава 4 
Рис. 4.7. 
Окно построителя выражений при формировании вычисляемого поля запроса 
Задача 2 
В вычисляемых полях и условиях отбора можно использовать 
встроенные функ-
ции
. В Access определено более 150 функций.
Пусть необходимо выбрать все накладные, по которым производилась отгрузка
в заданном месяце. В таблице НАКЛАДНАЯ дата отгрузки хранится в поле 
ДАТА_ОТГ
с типом данных 
Дата и время
(Date/Time).
1.
Создайте в режиме конструктора запрос на выборку для таблицы НАКЛАДНАЯ. 
Перетащите в бланк запроса поля 
НОМ_НАКЛ
и 
КОД_СК
(рис. 4.8). 
2.
Создайте вычисляемое поле в пустой ячейке строки 
Поле
(Field), записав туда 
одно из выражений:
•
Format([НАКЛАДНАЯ]![ДАТА_ОТГР];"mmmm")
— эта функция возвратит полное на-
звание месяца; 
•
или
Format([НАКЛАДНАЯ]![ДАТА_ОТГР];"mm")
— эта функция возвратит номер 
месяца. 
З
АМЕЧАНИЕ 
Если необходимо выделить не один элемент из даты, запишите в функции 
Format
не-
сколько символов форматирования, разделяя их знаком 
\
. Например, функция 
Format 
([НАКЛАДНАЯ]![ДАТА_ОТГР];”mmmm\ yyyy”) 
возвратит полное название месяца и че-
тыре цифры года. После знака 
\
нужно поставить пробел. 
3.
Для отбора накладных, выписанных в заданном месяце, в вычисляемом поле
в строку
Условие отбора 
(Criteria) введите название месяца, например 
март

---

Запросы 
165 
(рис. 4.8), или номер месяца, например 
3
, в соответствии с параметром в функ-
ции
Format
.
4.
Выполните запрос, нажав кнопку 
Выполнить 
(Run) в группе 
Результаты 
(Results) на вкладке ленты 
Работа с запросами | Конструктор
(Query Tools | 
Design). 
5.
Запишите в вычисляемом поле функцию 
Month(НАКЛАДНАЯ!ДАТА_ОТГ)
, и убеди-
тесь, что эта функция возвращает выделенный из даты номер месяца. 
6.
Для выборки всех строк, относящихся ко второму кварталу, в строку 
Условие 
отбора 
(Criteria) введите оператор 
Between 4 And 6
, определяющий, попадает ли 
значение выражения в указанный интервал. 
7.
Запишите в вычисляемом поле выражение 
MonthName(Month(НАКЛАДНАЯ!ДАТА_ОТГ))
и убедитесь, что функция 
MonthName
преобразует номер месяца в его полное на-
звание. 
Рис. 4.8. 
Запрос с функцией выделения из даты
полного названия месяца в вычисляемом поле 
Параметры в запросах 
В предыдущих примерах выражение в условие отбора вводилось в бланке запроса. 
При этом чтобы задать в условие отбора новое значение, нужно повторно открыть 
запрос в режиме конструктора и ввести его. При решении практических задач зна-
чительно удобнее вводить выражение в условие отбора в процессе выполнения за-
проса в диалоге с пользователем, не переходя в режим конструктора. Обеспечить 
такой диалог можно с помощью 
параметра запроса.
Имя параметра запроса зада-
ется в строке 
Условия
отбора
(Criteria) в квадратных скобках. При выполнении 
запроса это имя появится в диалоговом окне 
Введите значение параметра 
(Enter 
Parameter Value).

---

166 
Глава 4 
1.
Замените в условии отбора рассмотренного запроса (см. рис. 4.8) название меся-
ца март на имя параметра — 
[Название месяца]
.
2.
Выполните запрос. Открывшееся диалоговое окно (рис. 4.9) позволит ввести 
значение параметра запроса — 
Название месяца
. 
Рис. 4.9. 
Диалоговое окно ввода значения параметра 
3.
В запросе может быть определено несколько параметров. Например, для отбора 
записей по двум месяцам в условии отбора вычисляемого поля запишите два па-
раметра, связанных логической операцией 
OR
: 
[Название месяца] OR [Еще одно 
название месяца]
.
4.
Для определения числового интервала используйте параметры в операторе 
Between [Номер первого месяца периода] and [Номер последнего месяца периода]
. 
Параметры запроса могут быть использованы не только в выражениях условий от-
бора, но и для ввода значений операндов в вычисляемых полях.
1.
Создайте в режиме конструктора запрос на выборку для таблицы ТОВАР. Пере-
тащите в бланк запроса поля 
НАИМ_ТОВ
и 
ЦЕНА
.
2.
Для увеличения цены на заданный процент в вычисляемое поле запишите выра-
жение с параметром запроса 
[На сколько процентов увеличить?]
(рис. 4.10) — 
ЦЕНА+ЦЕНА*[На сколько процентов увеличить?]/100
. 
Рис. 4.10. 
Использование параметра в выражении вычисляемого поля 

---

Запросы 
167 
О
БРАТИТЕ ВНИМАНИЕ
Через этот запрос вы сможете показать увеличенные цены в таблице запроса или ис-
пользовать их при построении форм, отчетов, но они не будут внесены в поле табли-
цы ТОВАР. 
После выполнения запроса в таблице отображается результат вычисления с исполь-
зованием введенного значения параметра. Однако значение параметра при этом не 
выводится. Для отображения в таблице запроса введенного значения параметра до-
полните запрос еще одним вычисляемым полем, в котором запишите, например, 
такое выражение: 
Увеличение: "На " & [На сколько процентов увеличить?] & "%" 
Теперь в таблице запроса появится поле 
Увеличение
, в котором будет записано,
например, при вводе 
10
— 
На 10%
. В дальнейшем такое поле можно будет использо-
вать при создании форм или отчетов. 
Групповые операции в запросах
Групповые операции позволяют выделить группы записей с одинаковыми значе-
ниями в указанных полях и вычислить итоговые данные для каждой из групп по 
другим полям,
используя одну из статистических функций. Статистические функ-
ции применимы, прежде всего, к полям с типом данных 
Числовой
(Number), 
Де-
нежный
(Currency), 
Дата и время
(Date/Time). В Access предусматривается девять 
статистических функций.
Sum
— сумма значений некоторого поля для группы; 
Avg
— среднее от всех значений поля в группе; 
Max
, 
Min
— максимальное, минимальное значение поля в группе; 
Count
— число значений поля в группе без учета пустых значений; 
StDev
— среднеквадратичное отклонение от среднего значения поля в группе; 
Var
— дисперсия значений поля в группе; 
First
и 
Last
— значение поля из первой или последней записи в группе. 
Результат запроса с использованием групповых операций содержит по одной запи-
си для каждой группы. В запрос, прежде всего, включаются поля, по которым про-
изводится группировка, и поля, для которых выполняются статистические функ-
ции. Кроме этих полей в запрос могут включаться поля, по которым задаются усло-
вия обора. 
Рассмотрим конструирование однотабличного запроса с групповой операцией на 
примере таблицы ПОСТАВКА_ПЛАН.
Запрос с функцией 
Sum
Задача 
Определите, какое суммарное количество каждого из товаров должно быть постав-
лено покупателям по всем договорам. Все данные о запланированном к поставке 
количестве товара указаны в таблице ПОСТАВКА_ПЛАН. 

---

168 
Глава 4 
1.
Создайте в режиме конструктора запрос на выборку из таблицы ПОСТАВКА_ 
ПЛАН.
2.
Из списка таблицы перетащите в бланк запроса поле 
КОД_ТОВ
— код товара. По 
этому полю будет производиться группировка записей таблицы. 
3.
Перетащите в бланк запроса поле 
КОЛ_ПОСТ
, по которому будет подсчитываться 
суммарное количество каждого из товаров, заказанных во всех договорах.
4.
Выполните команду 
Итоги 
(Totals) из группы 
Показать или скрыть 
(Show/Hide). В бланке запроса появится новая строка 
Групповая операция
(Total) со значением 
Группировка
(Group By) в обоих полях запроса.
5.
В столбце 
КОЛ_ПОСТ
замените слово 
Группировка
(Group By) на функцию 
Sum
. 
Для этого вызовите список и выберите эту функцию. Бланк запроса примет вид, 
показанный на рис. 4.11. 
Рис. 4.11. 
Запрос с группировкой по коду товара
и суммированием количества в группе 
Рис. 4.12. 
Результат подсчета
суммарного количества каждого из товаров 
6.
Для отображения результата запроса (рис. 4.12) щелкните на кнопке 
Выполнить
(Run) в группе 
Результаты
(Results). 
7.
Замените подпись поля 
Sum-КОЛ_ПОСТ
на 
Заказано товаров
. Для этого пе-
рейдите в режим конструктора, в бланке запроса установите курсор мыши на 
поле 
КОЛ_ПОСТ
и нажмите правую кнопку. В контекстном меню выберите 
Свой-
ства
(Properties). В окне 
Свойства поля
(Field Properties) введите в строке
Подпись
(Caption) — 
Заказано товаров
. Для открытия окна свойств может быть
выполнена команда 
Страница свойств
(Property Sheet) в группе 
Показать или 
скрыть 
(Show/Hide). 
8.
Сохраните запрос под именем 
Заказано товаров
. 

---

Запросы 
169 
9.
Чтобы подсчитать количество товаров, заказанных в каждом месяце, выполните 
группировку по двум полям: 
КОД_ТОВ
и 
СРОК_ПОСТ
, в котором хранится месяц по-
ставки (рис. 4.13). 
Рис. 4.13. 
Запрос с группировкой по двум полям 
10.
Чтобы подсчитать количество товаров, заказанных в заданном месяце, преды-
дущий запрос дополните вводом параметра запроса в условие отбора 
(рис. 4.14). 
Рис. 4.14. 
Запрос с группировкой по двум полям и параметром запроса 
Запрос с функцией 
Count
Задача 
Определите, сколько раз отгружался товар по каждому из договоров. Факт отгрузки 
фиксируется в таблице НАКЛАДНАЯ.
1.
Создайте запрос на выборку на основе таблицы НАКЛАДНАЯ.
2.
Из списка полей таблицы НАКЛАДНАЯ перетащите в бланк запроса поле 
НОМ_ДОГ
. По этому полю должна производиться группировка.

---

170 
Глава 4 
3.
По сути, смысл задачи сводится к подсчету в таблице числа строк с одинаковы-
ми номерами договоров, поэтому неважно, по какому полю будет вычисляться 
функция 
Count
. Перетащите в бланк запроса любое поле, например, опять 
НОМ_ДОГ
.
4.
Выполните команду 
Итоги 
(Totals) из группы 
Показать или скрыть 
(Show/Hide). Замените слово 
Группировка
(Group By)
в одном из столбцов
с именем 
НОМ_ДОГ
на функцию 
Count
. Бланк запроса примет вид, показанный на 
рис. 4.15. 
Рис. 4.15. 
Запрос для подсчета числа отгрузок по договорам 
5.
Сохраните запрос под именем 
Число отгрузок по договорам
. Выполните запрос. 
Результат запроса показан на рис. 4.16. 
Рис. 4.16. 
Результат подсчета числа отгрузок по договорам 
Условия отбора в запросах с групповыми операциями 
В запрос с групповыми операциями помимо полей, по которым производится груп-
пировка и выполнение статистических функций для групп, можно из таблиц вклю-
чать поля для задания условий отбора записей, включаемых в группы.

---

Запросы 
171 
Задача 
Подсчитайте, сколько накладных было выписано по каждому из договоров, и како-
ва общая стоимость товаров, отгруженных по этим накладным. В расчете учиты-
вайте только накладные на сумму более 10 000 руб.
1.
Для подсчета общей стоимости товаров дополните бланк запроса 
Число отгру-
зок по договорам
(см. рис. 4.15, 4.16) полем 
СУММА_НАКЛ
и замените в нем слово 
Группировка
(Group By)
на функцию 
Sum
.
2.
Затем вторично включите поле 
СУММА_НАКЛ
в бланк запроса и замените слово 
Группировка
(Group By)
словом
Условие 
(Where),
выбрав его
из списка. 
В строку 
Условие отбора
(Criteria) введите выражение 
>10000
(рис. 4.17). 
Рис. 4.17. 
Запрос с групповой операцией для записей соответствующих условию отбора
3.
Выполните запрос и убедитесь, что число накладных для некоторых договоров 
уменьшилось, и общая стоимость по договору также считается только с учетом 
накладных на сумму более 10 000 (рис. 4.18). 
Рис. 4.18. 
Результат выполнения запроса 
4.
Чтобы отобрать только нужные группы записей, введите условие отбора в поле, 
по которому производится группировка, или в поле, где записана функция.
Например, чтобы отобрать договора с заданными номерами, введите в условие 
отбора поля 
НОМ_ДОГ
строку 
In ("Д111";"Д333")
. Чтобы отобрать договора с за-
данной общей стоимостью отгрузки по ним, введите в поле 
СУММА_НАКЛ
условие 
>100 000
. 
Отображение строки итогов по столбцу 
Строка итогов используется для быстрого расчета и отображения в столбце табли-
цы или запроса в режиме таблицы таких значений, как итоговая сумма, среднее, 
минимальное и максимальное, количество значений. 

---

172 
Глава 4 
1.
Для добавления строки итогов в таблицу запроса откройте запрос 
Число отгру-
зок по договорам
в режиме таблицы. На вкладке ленты 
Главная
(Home)
в группе 
Записи
выполните команду 
Итоги
(Totals). В таблице отобразится 
строка 
Итог
(Total).
2.
В строке 
Итог
(Total) нажмите кнопку со стрелкой вниз в столбце, для которого 
требуется выполнить расчет, и выберите в списке, например, 
Максимальное 
значение 
(Maximum). Результат выбора максимального значения в этом столбце 
показан на рис. 4.19. 
Рис. 4.19. 
Отображение строки итогов с расчетом максимума по столбцу 
З
АМЕЧАНИЕ
Кнопка со стрелкой вниз расположена с другой стороны ячейки для того, чтобы избе-
жать нарушений выравнивания текста и чисел. 
3.
Для того чтобы скрыть строку итогов, повторно выполните команду 
Итоги 
(Totals). 
Просмотр инструкции SQL в запросе 
1.
Откройте в режиме конструктора рассмотренный ранее запрос (см. рис. 4.5), со-
храненный под именем 
Пример2
. 
2.
Для перехода в 
Режим
SQL
(SQL View) выберите из списка кнопки 
Режим
(View) соответствующее значение. Можно также воспользоваться кнопкой 
Ре-
жим SQL
в строке состояния. Access выведет для запроса, созданного в режиме 
конструктора, эквивалентную инструкцию SQL (рис. 4.20). 
Рис. 4.20. 
Запрос с логическими операциями в условии отбора в режиме SQL 

---

Запросы 
173 
•
Инструкция 
SELECT
используется для формирования таблицы запроса, струк-
тура которой определяется выбранными из таблиц базы данных полями, а на-
бор записей сформулированными условиями отбора.
•
Список имен нужных полей следует непосредственно за ключевым словом 
SELECT
. 
•
Предложение 
FROM 
определяет таблицы или запросы, которые служат источ-
ником данных для данного запроса, и способ объединения их записей, если 
запрос построен на нескольких таблицах. 
•
В предложении 
WHERE
задаются условия отбора записей, которые полностью 
соответствуют сформулированным в задании на создание запроса (см. рис. 4.5). 
•
Предложение
ORDER BY 
указывает, по каким полям должна быть выполнена 
сортировка записей в таблице запроса. При отсутствии сортировки этого 
предложения нет в инструкции. Данная инструкция указывает на сортировку 
по возрастанию по полю
ЦЕНА 
из таблицы
ТОВАР
. 
3.
Перейдите в режим конструктора, внесите изменения в бланк запроса, например, 
добавьте в поле код товара — 
КОД_ТОВ
. 
4.
Вернитесь в режим SQL и посмотрите, как изменилась инструкция SQL. 
5.
Чтобы убедиться, что запросы можно редактировать в режиме SQL, а не только 
в режиме конструктора, внесите изменения в инструкцию SQL, удалив из списка 
полей 
КОД_ТОВ
. 
6.
Затем перейдите в режим конструктора и убедитесь, что из бланка запроса уда-
лено поле 
КОД_ТОВ
. 
7.
Откройте в режиме конструктора запрос 
Число отгрузок по договорам
, в кото-
ром используется только функция подсчета числа договоров. 
8.
Перейдите в режим SQL. Инструкция SQL в этом запросе будет записана, как 
показано на рис. 4.21.
Рис. 4.21. 
Запрос с использованием функции 
Count
в режиме SQL
В инструкции 
SELECT
определен список полей для таблицы запроса. Это поле 
НОМ_ДОГ
, по которому производится группировка, и вычисляемое поле, содержащее 
результат выполнения функции 
Count
, примененной к полю 
НОМ_ДОГ
. Зарезервиро-
ванное слово 
AS
позволяет определить для поля с результатом вычисления псевдо-
ним 
Count-НОМ_ДОГ
, который станет заголовком столбца. 
Обратите внимание — в инструкции наряду с именами полей, включаемых в ре-
зультат, допускается использование функций. Возможно включение и более слож-
ных выражений, определяющих содержимое вычисляемых полей. 

---

174