Итоговые запросы

Итоговые запросы позволяют производить итоговые вычисления по группам данных. Создание итогового запроса происходит в 2 этапа:

1. Группирование данных по значениям определённого поля.

§ Выбрать коман­ду Вид † Групповая операция или в режиме Конструктора запроса нажать кнопку Группо­вые операции на панели инструментов.

§ Обратить внимание на появление строки «Групповая операция» в бланке запроса. Команда Групповая операция дает установку Группировка для любого поля, занесённого в бланк запроса. В этом случае записи таблицы группируются, но итоги не подводятся.

G Примечание. Поле, значения которого нужно группировать, желательно поставить первым в списке выводимых полей.

2. Вычисление итогов в группе.

Заменить в соответствующем поле установку Группировка в строке Групповая операция на конкретные итоговые функции. Задать нужную функцию можно, указав её в раскрывающемся списке Группировка.

G Примечания.

1) MS Access предоставляет 9 статистических функций SQL, обеспечивающих выполнение групповых операций (табл.1).

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

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

4) Группировка в двух и более полях означает вложенное группирование, т.е. создание группы внутри группы, заданной в предыдущем столбце (см. ниже пример 4).

Таблица 1. Статистические функции SQL.

Примеры итоговых запросов:

1. Вычислить, сколько и на какую сумму продано товаров каждым продавцом.

Выполнение:

- Ввести в запросный бланк поля: ФИО (табл. Продавец), Количество (табл. Продажи), Цена (табл. Товар);

- Создать вычисляемое поле Стоимость с помощью Построителя выражений (формула приведена на рис. 5);

- сформировать группы по полю ФИО из таблицы Продавец ( - Групповая операция);

- в поле Количество и Стоимость ввести функцию Sum;

- сохранить запрос под именем «Итоги».

Рис.5. Бланк итогового запроса

Рис.6. Результат итогового запроса

2. В итоговые запросы можно включать Условие отбора, причём с применением статистических (групповых) функций.

Пример: вывести список только тех продавцов, стоимость продаж которых больше средней стоимости.

- Добавить в запрос Итоги условие:

> (SELECT AVG([Стоимость]) From Итоги)

Рис.7. Бланк итогового запроса с условием и результат запроса.

3. Вывести цену самого дорогого товара, проданного каждым продавцом.

Выполнение:

- Ввести в запросный бланк поля ФИО (табл. Продавец), Цена (табл. Товар);

- группы сформировать по полю ФИО из таблицы Продавец ( - Групповая операция);

- в поле Цена задать функцию Мах (максимальное значение поля);

- установить порядок сортировки в поле Цена – по убыванию

- сохранить запрос под именем МахЦена.

Рис.8. Бланк запроса и результат запроса МахЦена

4. Вывести итоговую сумму каждого проданного товара отдельно для каждого продавца.

Выполнение:

- Выполнить запрос на основе запроса «Стоимость» - открыть его в режиме Конструктора;

- включить группировку – - Групповая операция;

- в поле СтоимостьПартии ввести функцию Sum;

·

Рис.9. Бланк итогового запроса (пример 4).

- проверить работу запроса;

- сохранить запрос под другим именем – Файл † Сохранить как †

Итог_Стоимость.

G Примечание к примеру 4.

1) В примере используется вложенное группирование:

- вначале группируются фамилии продавцов,

- внутри группы совпадающих значений ФИО создаются группы одинаковых названий товаров,

- затем вычисляются суммы по группе товаров.

2) Данный пример является примером многоэтапного запроса, т.е. выполняется запрос к таблице, являющейся результатом предыдущего запроса (1-й этап – запрос «Стоимость», 2-й этап – группирование данных в таблице запроса «Стоимость», результат – запрос «Итог_Стоимость»).