Вычисления на всем диапазоне записей таблицы

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

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

1. Создайте новый запрос и добавьте в него таблицы "Заказы" и "Заказано".

2. Перетащите поля "КодЗаказа" и "ДатаРазмещения" таблицы "Заказы", соответственно в первое и второе поля бланка запроса.

3. Введите в ячейку строки "Поле" третьего (пустого) столбца бланка запроса выражение Итог: Sum([Количество]*[Цена]*(1-[Скидка])), которое вычисляет чистую сумму товаров, включенных в поставку. Установив курсор в столбец "Итог", нажмите на панели инструментов кнопку "Свойства" и присвойте значение "Денежный" свойству поля "Формат поля".

4. Нажмите на панели инструментов кнопку "Групповые операции". В бланк запроса добавляется строка "Групповая операция", содержащая по умолчанию в каждой ячейке операцию "Группировка".

5. Выберите из списка статистических функций SQL в третьей ячейке строки "Групповая операция" элемент "Выражение". Нажмите кнопку "Запуск" на панели инструментов, чтобы проверить запрос.

Закройте запрос, сохранив его под именем "Итоги по заказам".

Чтобы использовать статистические функции SQL для поля "Итог" запроса "Итоги по заказам":

1. Создайте новый запрос и добавьте в него запрос "Итоги по заказам".

2. Перетащите поле "КодЗаказа" на первый столбец, а затем четыре раза поле "Итог".

3. Выберите команду "Вид, Групповые операции" для добавления на бланк запроса строки "Групповая операция".

4.Установите курсор в столбец "КодЗаказа" строки "Групповая операция" и выберите из списка функцию Count (). Установите курсор в первый столбец "Итог" строки "Групповая операция" и выберите из списка функцию Sum(), для второго столбца "Итог" задайте функцию Avg(), для третьего — функцию Min (), для четвертого — функцию Мах ().

5.Установите курсор в столбец "КодЗаказа" и нажмите на панели инструментов кнопку "Свойства". Присвойте значение "Счетчик" свойству "Подпись".

6.Повторите шаг 5 для каждого из четырех столбцов "Итог", присвоив значение "Денежный" свойству "Формат поля", a свойству "Подпись" значения "Суммарная выручка", "В среднем за заказ", "Минимум", "Максимум" соответственно. Значение свойства "Формат поля" задавать не требуется, если в запросе "Итоги по заказам" использована функция ccur ().

Нажмите кнопку "Запуск" (Run) для вывода результирующего множества. Условие отбора не было указано, поэтому выведенные данные относятся ко всей таблице. Сохраните запрос под именем "Статистика по заказам".

Вычисления на выделенных записях таблицы

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

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

2. Перетащите поле "ДатаРазмещения" на столбец "КодЗаказа", чтобы столбец "ДатаРазмещения" стал первым в бланке запроса. Теперь необходимо определить условие отбора заказов на основе даты.

3. Замените элемент "Группировка" в столбце "ДатаРазмещения" на элемент "Условие". Access сбрасывает флажок "Вывод на экран" столбца "ДатаРазмещения". Если установить этот флажок, то при выполнении запроса будет выведено сообщение об ошибке: этого нельзя делать в столбце, который явно связан с предложением SQL where.

Введите выражение Like "3/*/08" в ячейку "Условие отбора" столбца "ДатаРазмещения" для отбора заказов, размещенных в марте 2008 года. Выберите команду "Вид, Режим SQL" для вывода предложения SQL. В него добавлена операция WHERE, в данном случае where ((([Итоги по заказам].ДатаРазмещения) Like "3/*/08", которая отбирает требуемые записи. Если не задать в ячейке "Групповая операция" элемент "Условие отбора", то выводятся обобщенные данные для каждого дня марта 2008 года, а не для всего месяца. Данные можно обобщить более удобным образом с помощью функции Format (), например, чтобы получить итоговые данные по месяцам (или по кварталам). Для вывода информации о продажах по каждому месяцу 2008 года:

1. Нажмите на панели инструментов кнопку "Конструктор", а затем щелкните по области выделения столбца "ДатаРазмещения", чтобы выделить его. Добавьте в запрос пустой столбец.

2. Введите выражение Месяц: Format ([ДатаРаЗмещения]; "yy-mm") В первый (пустой) столбец строки "Поле". Формат "yy-mm" используется для упорядочивания записей по дате. Кроме того, можно применять формат "т" или "mm", но не "ттт", потому что в последнем случае столбцы упорядочиваются в алфавитном порядке, начиная с августа.

Введите выражение Like "*/*/08" в ячейку "Условие отбора" столбца "ДатаРазмещения" Нажмите на панели инструментов кнопку "Запуск" для вывода результирующего множества. Выберите команду "Вид, Режим SQL" для вывода предложения SQL.

Выберите команду "Файл, Сохранить как" (File, Save As) для сохране­ния запроса под другим именем, таким, например, как "Ежемесячная статистика по заказам".