Задание 2. Групповые операции в запросах

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

Групповые операции (группировка) позволяют выделить группы записей с одинаковыми значениями в указанных полях и использовать для некоторых полей этих групп одну из статистических функций.

В MS Access предусмотрено девять статистических функций:

― SUM – сумма значений некоторого поля для группы;

― AVG – среднее от всех значений поля в группе;

― MAX, MIN – максимальное, минимальное значение поля в группе;

― COUNT – число значений поля в группе без учета пустых значений;

― StDev – среднеквадратичное отклонение от среднего значения поля в группе;

― VAR – дисперсия значений поля в группе;

― First, Last – значение поля из первой или последней записи в группе.

Результат запроса с использование групповых операций содержит по одной записи для каждой группы.

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

Рассмотрим технологию создания запроса с группировкой записей и применения статистической функции к сгруппированным записям.

Создадим запрос для вычисления количества (COUNT) различных товаров в каждой группе товаров (типе товаров).

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

1. В режиме КОНСТРУКТОРА создайте запрос для таблиц ТОВАР и ТИП_ТОВАРА. Таблица ТИП_ТОВАРА нам потребуется для расшифровки значений поля КОД_ТИПА (рис.3).

2. Перенесите в бланк запроса поля ТИП_ТОВАРА из таблицы ТИП_ТОВАРА и НАИМЕНОВАНИЕ из таблицы ТОВАР (рис.3).

3. Нажмите кнопку на панели инструментов  ИТОГИ.

4. Для поля НАИМЕНОВАНИЕ в строке ГРУППОВАЯ ОПЕРАЦИЯ щелкните мышью в правом углу поля (должен появиться элемент управления список) и выберите из раскрывающегося списка операцию COUNT (рис.3).

Рис.3. Групповые операции в запросах

Запустите запрос на исполнение и убедитесь в появлении групповых итогов (рис.4).

Рис.4. Итоги групповой операции

Можно изменить название поля Count_НаименованиеТовара на любое другое, например, КОЛИЧЕСТВО_НАИМЕНОВАНИЙ.

5. Вернитесь в режим КОНСТРУКТОРА, щелкните мышью в названии поля НАИМЕНОВАНИЕ_ТОВАРА и введите КОЛИЧЕСТВО_НАИМЕНОВАНИЙ до названия поля, поставьте знак двоеточие (:) для разделения позиций (мы обсуждали с вами этот вопрос).

6. Запустите запрос на исполнение и убедитесь в появлении нового названия поля.

Можно применять несколько статистических функций одновременно.

7. Добавьте в бланк запроса еще одно поле – МИНИМАЛЬНЫЙ_ЗАПАС.

8. Примените (выберите из списка) для этого поля функцию AVG(), вычисляющую среднее от всех значений поля в группе.

9. Измените название поля и просмотрите результаты выполнения запроса (рис.5).

Рис.5. Запрос с несколькими статистическими функциями

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

10. Измените для поля МИНИМАЛЬНЫЙ_ЗАПАС функцию AVG() на значение УСЛОВИЕ в том же раскрывающемся списке (рис.6).

11. Введите значение условия. Запустите запрос на исполнение.

Обратите внимание на отсутствие флажка ВЫВОД НА ЭКРАН, это особенность задания условий в групповых запросах.

Рис.6. Использование условий отбора в запросах с групповыми операциями

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

Создадим запрос, вычисляющий стоимость всего заказа и внедрим его в форму ЗАКАЗ.

12. В режиме КОНСТРУКТОР создайте запрос для таблицы ПУНКТ_ЗАКАЗА.

13. Переместите поле НОМЕР_ЗАКАЗА в бланк запроса и включите для этого поля операцию ГРУППИРОВКА.

14. В пустом поле щелкните указателем мыши и вызовите ПОСТРОИТЕЛЬ.

15. Используйте встроенные функции для ввода вычисляемого выражения (рис.7).

Рис.7. Создание вычисляемого поля

16. Переименуйте Выражение1, например, в СТОИМОСТЬ_ЗАКАЗА.

17. Нажмите ОК в ПОСТРОИТЕЛЕ выражений.

18. Выберите в поле ГРУППОВАЯ ОПЕРАЦИЯ для нового поля строку ВЫРАЖЕНИЕ из раскрывающегося списка (рис.8).

Рис.8. Создание вычисляемого поля в запросе с группировкой

19. Запустите запрос на исполнение и убедитесь в выполнении группировки и вычислений (рис.9).

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

20. Сохраните запрос, назовите его, например, Стоимость_заказа. Закройте созданный запрос.

21. Раскройте форму ЗАКАЗ в режиме КОНСТРУКТОРА, определите место расположения для внедренного запроса.

22. Возьмите указателем мыши запрос и перетащите его в приготовленное место в форме ЗАКАЗ.

23. В открывшемся диалоговом окне МАСТЕРА подчиненных форм (рис. 10) укажите режим САМОСТОЯТЕЛЬНОЕ ОПРЕДЕЛЕНИЕ для установления связи между формой и внедренным в нее запросом.

24. В раскрывающихся списках выберите поля для связи (рис.10).

25. Нажмите кнопку ДАЛЕЕ, дайте подчиненной форме (запрос становится подчиненной формой) осмысленное название и нажмите ГОТОВО.

Рис.10. Первый шаг МАСТЕРА подчиненных форм

Рис.11. Второй шаг МАСТЕРА подчиненных форм

26. Откройте форму ЗАКАЗ в режиме ВИД (рис.12).

27. С помощью СВОЙСТВ измените внешний вид внедренной формы (запроса). Можно, например, выключить изображения линии прокрутки, элемента выбора и так далее (рис. 12).

28. Поле НОМЕР_ЗАКАЗА можно свернуть.

29. Проверьте работоспособность измененной формы: введите несколько новых позиций в имеющиеся заказы и создайте несколько новых заказов.

Рис.12. Окончательный вид формы ЗАКАЗ с внедренным запросом