Итоговые и модифицирующие запросы

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

Итоговые запросы отличаются от обычных запросов на выборку. В них поля делятся на 2 типа:

· поля, по значениям которых производится группировка данных (поле «Наименование», рисунок 17.1);

· поля, по значениям которых проводятся вычисления (поля «Количество», «Сумма», рисунок 17.1);

Поля, не относящиеся к вышеперечисленным типам (поля «Цена», «Единица» и «Дата», рисунок 17.1), исключаются из состава полей итогового запроса.

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

Для вычисления итоговых значений используются агрегатные функции стандарта SQL, основными являются следующие:

§ SUМ(Имя поля) – сумма значений указанного поля;

§ AVG(Имя поля) – среднее значение для записей указанного поля;

§ MIN(Имя поля) – минимальное значение в определенном поле;

§ MAX(Имя поля) – максимальное значение в определенном поле;

§ COUNT(*) – количество записей;

В MS Access могут быть использованы функции расширения, отличные от функций предусмотренных стандартом, например:

§ FIRST(Имя поля) – первое значение в указанном поле;

§ LAST(Имя поля) – последнее значение в указанном поле.

Рассмотрим пример группировки на основе таблицы «Продажи товаров» (рисунок 17.1), из которой необходимо получить информацию об итогах продаж каждого товара. Для каждой группы товаров должны быть рассчитаны: итоговая сумма продаж и количество единиц проданного товара. Таким образом, проводим группировку по полю «Наименование», вычисляем суммы для полей «Количество» и «Сумма» (рисунок 17.1). Прочерком помечены поля, которые не используются при подведении итогов и исключаются из запроса.

Рисунок 17.1 – Таблица «Продажи товаров»

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

Рисунок 17.2 – Результат выполнения группировки

Итоговые запросы средствами MS Access могут быть созданы средствами мастера или конструктора.

Процесс создания итогового запроса с помощью средства «Простой запрос» сводится к следующим шагам:

§ Запускается мастер создания простых запросов, например командой – Создание запроса с помощью мастера

§ в раскрывающемся списке «Таблицы и запросы» последовательно выбираются таблицы или запросы, информация из которых необходима пользователю, а затем, из списка «Доступные поля» в список «Выбранные поля» перемещаются требуемые поля.

§ На втором шаге работы мастера определяется тип запроса «Итоговый». Нажимаем кнопку «Итоги…» и в окне диалога «Итоги» (рисунок 17.3), выбираем способы вычисления итогов: Max, Min, Sum, Avg или Count (подсчет числа записей).

Рисунок 17.3 – Фрагмент окна диалога «Итоги»

§ На третьем шаге указываем имя запроса и один из вариантов действий: открыть запрос для просмотра данных или изменить макет запроса, в первом случае результаты запроса будут выданы на экран, а во втором, запрос откроется в режиме конструктора.

Для создания итогового запроса в режиме конструктора можно использовать кнопку «Групповые операции» на панели инструментов или воспользоваться командой Вид - Групповые операции.

В результате, в бланке запроса появится новая строка – «Групповая операция». Если для соответствующего поля из списка выбрать функцию Группировка (рисунок 17.4), то при выполнении запроса записи будут объединяться по этому полю и вся группа будет представлена одной строкой.

Рисунок 17.4 - Строка «Групповая операция»в бланке QBE

Для всех остальных полей необходимо определить итоговые функции, как изображено на рисунке 17.5.

Рисунок 17.5 – Определение итоговых функций для полей запроса

Группировка и итоговые выражения могут проводиться не только по реально существующим полям, но и по вычисляемым в запросе выражениям. Например, если сумма продажи товара не задана явно, однако присутствуют поля «Количество» и «Цена единицы», то сумма может быть получена как:

[Цена] * [Количество]

В этом случае итоговую сумму продаж можно получить выражением:

Сумма: Sum (Количество] * [Цена])

На рисунке 17.5 поле «Сумма» получено с использованием вышеуказанного метода.

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