Групповые операции и вычисления

Как правило, при работе с базой данных не обойтись без некоторых итоговых вычислений, позволяющих получить, например, общую сумму заказов, количество проданных товаров, поквартальное распределение выручки и многое другое. Включить выполнение некоторых вычислений можно с помощью еще одной строки бланка запроса, Групповая операция, которая изначально не видна в окне конструктора. Чтобы она отобразилась выберите опцию Вид/Групповые операции. Теперь в бланке запроса можно выбрать для любого поля нужный вариант вычислений, определить среднюю величину числовых данных или сгруппировать данные по этому полю.

Список поля Групповая операция:

1. Группировка.Этот элемент указывает на поле, по которому результат выполнения запроса будет организован в группу для дальнейших итоговых вычислений.
2. Sum. Суммируются значения поля запроса.
3. Avg.Находится среднее значение поля запроса.
4. Min.Находится минимальное значение поля запроса.
5. Max.Находится максимальное значение поля запроса.
6. Сount. Вычисляется количество непустых ячеек в поле запроса.
7. StDev.Вычисляется среднеквадратичное отклонение для данного поля запроса.
8. Var. Вычисляется дисперсия значений данного поля.
9. First. Отображается значение поля из первой записи результирующего набора.
10. Last. Отображается значение поля из последней записи результирующего набора.
11. Выражение.Этот элемент сообщает программе Access, что следует создать поле, значение которого будет вычисляться.
12. Условие. Этот элемент указывает программе Access, что данное поле не участвует в группировке. Условие, вводимое в это поле, определяет, какие записи будут участвовать в вычислениях.

Приступим к созданию запросов с групповыми вычислениями.

Запрос итог по ценам товаров:Определить количество разновидностей товаров в вашей фирме, вывести максимальную, минимальную, среднюю цены товаров, вычислить дисперсию, среднее квадратичное отклонение цен товаров.

Откройте конструктор запросов, добавьте в него таблицу Товары. Поместите поля КодТовара и 5 раз ЦенаТовара в строку Поле. Оформите бланк запроса так, как показано на рис. 12:

(Рис. 12)

Выполните запрос и сохраните его под именем ИтогиПоЦенеТоваров.

Запрос по количеству сотрудников на должностях:Определить, сколько сотрудников находится на каждой должности.

Откройте конструктор запросов, добавьте в него таблицу Сотрудники. Поместите поля КодСотрудника и Должность в строку Поле. Оформите бланк запроса так, как показано на рис. 13:

(Рис. 13)

Запустите запрос и сохраните по именем ЗапросПоДолжности.

Запрос по количеству сотрудников на должности менеджера и администратора (группировка с условием):Определить, сколько сотрудников находится на должности менеджера и администратора соответственно.

Откройте конструктор запросов, добавьте в него таблицу Сотрудники. Поместите поля КодСотрудника и Должность в строку Поле. Оформите бланк запроса так, как показано на рис. 14.

(Рис. 14)

Выполните запрос и сохраните его под именем ЗапросПоДвумДолжностям.

Если возникает необходимость отобрать по некоторому критерию записи еще до групповых операций, то для поля Групповая операция нужно в отдельном столбце выбрать элемент Условие и в строке Условие отбора следует задать это условие.

Например, нас интересуют сотрудники, домашний телефон которых начинается с числа 78, а среди них нужно найти число сотрудников на каждой должности. Для создания такого запроса откройте конструктор запросов, добавьте в него таблицу Сотрудники. Поместите поля КодСотрудника, Должность, ДомашнийТелефон в строку Поле. Оформите бланк запроса так, как показано на рис. 15.

(Рис. 15)

Выполните запрос и сохраните под именем Тел78.

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

Запрос на сумму цен товаров в заказах:Создать результирующую таблицу, отображающую список заказов, в котором указано какие товары на какую сумму были заказаны и дата размещения заказа.

Откройте конструктор запросов, добавьте в него таблицы Заказы, Сведения о заказе, Товары. Поместите поля Код заказа и ДатаРазмещения из таблицы Заказы, Код товара и Количество из таблицы Сведения о заказе, Цена из таблицы Товары в строку Поле. В шестом столбце строки Поле запишите выражение: «сумма: [Сведения о заказе]![Количество]*[Сведения о заказе]![Цена]». Выполните запрос, сохраните под именем Запрос_на_товары_в_заказах.

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

Запрос на сумму заказанного клиентами в конкретный период времени:Вывести таблицу, в которой указывалась бы фамилия и имя клиента, количество заказов, сумма заказанного (без налогов и скидок), но при условии, что заказы сделаны в период с 1 января 2008 года по 1 января 2009 года.

В режиме конструктора запросов добавьте таблицы Клиенты, Заказы, Сведения о заказе. Оформите бланк запроса, как показано на рис. 16.

(Рис. 16)

Указание: «сумма: Sum([Сведения о заказе]![Количество]*[Сведения о заказе]![Цена])».

Третий столбец называется сумма, тип групповой операции – выражение, т.е. это вычисляемое поле. Оно суммирует произведения цен товаров на их заказанное количество по всем заказам, относящимся к конкретному клиенту, по фамилии которого и происходит группировка.В первом столбце будет показано количество заказов, сделанных конкретным клиентом. Условие отбора по дате записано в четвертом столбце: >=#01.01.2008# And <=#01.01.2009#. Настройте маску для вычисляемого поля. Для этого щелкните правой кнопкой мыши по вычисляемому полю суммазаказанного и выберите из списка Свойства. На вкладке общие выберите формат поля - денежный, число знаков после запятой – Авто, маска ввода - #\ ##0\,00"руб.". О масках ввода речь шла в предыдущей лабораторной работе Расписание. На данный момент вы четко должны представлять, что оно обозначает.Выполните запрос и сохраните его под именем ЗапросПоСуммеЗаказанного.