Задание 8. Групповая обработка данных в запросах.
Групповая обработка данных в запросах без условий отбора.
Цель задания создать запрос, содержащий информацию о средней цене (без учета стоимости доставки) на каждую модель товара и о суммарном объеме заказов на каждую модель товара.
Методические указания:
Создать новый запрос в режиме Конструктора. Выбрать таблицы Группы, Товары и Заказы для использования в запросе.
Ввести в бланк запроса строку Групповая операция.
Выбрать поля Наименование_группы, Модель и ввести их в бланк запроса. В строке Групповая операция для этих полей установить значение Группировка.
В первом свободном столбце бланка запроса ввести в строке Поле следующую формулу: Ср_цена: Avg( [Опт_цена] *(1+0,01* [Торг_надбавка])) В строке Групповая операция необходимо выбрать из списка пункт Выражение. Вывести на экран контекстное меню для этого столбца и выбрать из него команду Свойства. В окне «Свойства поля» выбрать формат поля – Фиксированный, число десятичных знаков – 2.
В следующем свободном столбце в строке Поле ввести формулу: Объем_заказов: Размер_заказа В строке Групповая операция необходимо выбрать из списка функцию Sum.
Сохранить созданный запрос под именем Характеристики_заказов.
Запустить запрос на выполнение и просмотреть результаты.
Применение условий отбора в запросах с групповыми операциями. Цель задания создание запросов с двумя различными вариантами использования условий отбора при наличии групповых операций.
Методические указания:
1 вариант: вначале отфильтровываются записи, удовлетворяющие заданным условиям отбора, и затем над ними выполняются групповые операции. В этом случае для полей, по которым задаются условия отбора, в строке Групповая операция вводится значение Условие, а условия отбора обычным образом помещаются в соответствующей строке. В качестве примера предлагается создать запрос, содержащий суммарный объем заказов по каждому типу и группе товаров (Ксерокс Персональный и т. д.) при условиях: 1)срок гарантии не менее 1,5 года; 2)предоставление дополнительных услуг.
Создать новый запрос в режиме Конструктора. Выбрать таблицы Группы, Товары и Заказы для использования в запросе.
Ввести в бланк запроса строку Групповая операция.
Выбрать поля Наименование_группы из таблицы Группы и Тип_ товара из таблицы Товары и ввести их в бланк запроса. В строке Групповая операция ввести значение Группировка и установить режим вывода этих полей на экран.
В следующем свободном столбце в строке Поле ввести формулу Объем_заказов: Размер_заказа В строке Групповая операция для этого поля выбрать функцию Sum.
Выбрать из таблицы Заказы поля Срок_гарантии и Доп_услуги и ввести их в бланк запроса. Установить для этих полей значение Условие в строке Групповая операция. В строке Условие отбора для поля Срок_гарантии ввести >=1,5; для поля Доп_услуги ввести Да. Отменить вывод этих полей на экран.
Сохранить запрос под именем Груп1.
Запустить запрос Груп1 на выполнение и просмотреть результаты.
2 вариант: вначале выполняются групповые операции по отношению ко всем записям; фильтрация на основе заданных условий отбора производится над записями, полученными в результате выполнения групповых операций. В этом случае условия отбора указываются в соответствующей строке для тех полей, над которыми предварительно выполнялись групповые операции; поэтому в строке Групповая операция указано Группировка или соответствующая математическая операция (Avg, Sum и т. д.). В качестве примера предлагается создать запрос, содержащий суммарный объем заказов по каждому типу и группе товаров при условии, что заказано не менее 10 изделий данного типа.
Повторить действия, указанные в пп. 2.12.3.
В первом свободном столбце ввести поле Объем_заказов, аналогично п. 2.5. При этом в строке Условие отбора ввести >=10. Бланк запроса имеет вид:
Сохранить запрос под именем Груп2.
Запустить запрос Груп2 на выполнение и просмотреть результаты.
Задачи для самостоятельного решения.
Создать запрос, в результатах которого вывести список клиентов и суммарную стоимость заказов каждого из них.
Создать запрос, в результатах которого вывести список клиентов и суммарную стоимость заказов каждого из них при условии, что в заказах этих клиентов есть модели товаров с номерами от 400 до 499 (т.е. ксероксы моделей C400GLS,…, C499GLS или факсы моделей F400G,…, F499G).
Создать запрос, в результатах которого вывести суммарный объем заказов по каждой группе товаров, для которых оптовая цена находится в диапазоне значений от 10000 р. до 30000 р.
Создать запрос, в результатах которого вывести список клиентов, суммарный объем заказов и суммарную стоимость заказов каждого из них, при условии, что суммарный объем заказов клиента составляет от 50 до 200 единиц.