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


Первый запрос, который мы предлагаем построить, должен ответить на вопрос: "Сколько заказов и на какую сумму разместил каждый клиент?". Для этого нам придется построить многотабличный запрос. Воспользуемся уже существующим в базе данных "Борей" запросом "Сведения о заказах" (Oreder Details Extended), а также таблицами "Клиенты" (Customers) и "Заказы" (Orders):

  1. Создайте новый запрос и добавьте в него таблицы "Клиенты", "Заказы" и запрос "Сведения о заказах".
  2. Перетащите в бланк заказа поле "Название" из таблицы "Клиенты", поле "КодЗаказа" из таблицы "Заказы", а затем поле "Отпускная цена" из запроса "Сведения о заказах".
  3. Нажмите кнопку Групповые операции (Totals) на панели инструментов. В бланк запроса добавляется строка Групповая операция (Total), содержащая по умолчанию в каждой ячейке операцию Группировка (Group By).
  4. Теперь нужно в строке Групповая операция (Total) задать необходимые статистические функции. Для поля "Название" оставьте значение Группировка (Group By), для поля "КодЗаказа" выберите из списка функцию count, а для поля "Отпускная цена" — Sum.
  5. Нажмите кнопку Запуск (Run) на панели инструментов, чтобы посмотреть результаты запроса. Вы получите таблицу, которая содержит список клиентов, и для каждого клиента указывается количество заказов, которое он сделал, и общая сумма этих заказов.
  6. Закройте запрос, сохранив era под именем "Итоги по клиентам".

Вычисления на выделенных записях таблицы
В предыдущем примере расчеты производились над всеми имеющимися в таблице "Заказы" (Orders) записями. Однако часто требуется выполнить вычисления над определенным набором данных и, основываясь на них, составить статистику. Для этого нужно добавить в запрос условия для выборки нужного набора записей. Например, мы можем изменить предыдущий запрос таким образом, чтобы получить данные не по всем клиентам, а только по тем, которые находятся в Германии. Для этого:

  1. Откройте запрос "Итоги по клиентам", который мы построили в предыдущем разделе, в режиме Конструктора.
  2. Перетащите поле "Страна" (Country) из таблицы "Клиенты" (Customers) в бланк запроса. В строке Групповая операция (Total) для этого поля появляется элемент Группировка (Group by).
  3. Замените элемент Группировка (Group by) на элемент Условие (Where), выбрав его из списка. При этом автоматически сбрасывается флажок (Show) в данном столбце. Это означает, что в результат запроса поле "Страна" не попадет. Если вы попытаетесь установить этот флажок, то при выполнении запроса будет выведено сообщение об ошибке. Действительно, если мы хотим, чтобы поле "Страна" отобразилось в результате запроса, его нужно добавить в бланк запроса второй раз, при этом в строке Групповая операция (Total) данного столбца нужно оставить элемент Группировка (Group by) и установить флажок Вывод на экран (Show).
  4. Введите слово Германия в ячейку Условие отбора (Criteria) первого столбца "Страна" (того, который не выводится на экран).
  5. Выберите команду Вид, Режим SQL (View, SQL View) или щелкните по стрелке на кнопке Вид (View) и выберите значение Режим SQL (SQL View) для вывода инструкции SQL. Она будет выглядеть следующим образом: SELECT Клиенты.Название, Count(Заказы.КодЗаказа) AS [CountOfKoд3a каза], Sum([Сведения о заказах].ОтпускнаяЦена) AS [SumOfОтпускная Цена], Клиенты.Страна, Клиенты.Страна FROM Клиенты INNER JOIN ([Сведения о заказах] INNER JOIN Заказы ON [Сведения о заказах].КодЗаказа = Заказы.КодЗаказа) ON Клиенты.КодКлиента = Заказы.КодКлиента WHERE (((Клиенты.Страна)="Германия")) GROUP BY Клиенты.Название, Клиенты.Страна;

В ней используются две статистические функции— Count () и Sum () — и предложение WHERE, содержащее условие отбора записей.

  1. Измените это предложение, заменив названия полей, в которых ведутся расчеты: AS [Число заказов] вместо AS [CountOfКодЗаказа], AS [Общая сумма] вместо AS [Sum()?ОтпускнаяЦена].
  2. Нажмите на панели инструментов кнопку Запуск (Run) для вывода результата. Результирующий набор записей представлен на.

Статистические функции можно использовать также в вычисляемых полях запроса. Например, для создания запроса "Итоги по клиентам" мы могли бы нместо запроса "Сведения о заказах" использовать прямо таблицу "Заказано". На показано вычисляемое поле "Стоимость". В выражение для этого поля включена функция Sum ():
Стоимость: Sum(CCur([Заказано].[Цена]*[Количество]*(1-[Скидка])/100) *100)
Тогда в строке Групповая операция (Total) для этого столбца должен быть выбран элемент Выражение (Expression). При использовании группировки по полю "КодЗаказа", поле "Стоимость" будет содержать общую стоимость заказа.
Можно создать и другое вычисляемое поле:
Стоимость: CCur([Заказано].[Цена]*[Количество]*(1-[Скидка])/100) *100,
а функцию Sum () использовать в строке Групповая операция (Total). Оба варианта будут правильны.
Можно изменить этот запрос таким образом, чтобы посчитать общее число клиентов, заказов и общую сумму всех заказов. В этом случае результатом запроса будет одна строка, содержащая итоговые значения по всем полям. К сожалению, нельзя построить запрос, результатом которого была бы таблица, содержащая как детальные, так и итоговые данные, как это обычно бывает в таблицах Excel. Такое представление данных можно организовать только в сводных таблицах.
Внимание

Статистические функции при вычислениях не обрабатывают записи, которые имеют значение Null в том поле, к которому они применяются. Поэтому нужно быть осторожным при использовании функции Count (). Она будет считать только те записи, которые не содержат пустых значений. Если нужно сосчитать все записи в результирующем наборе или группе, используйте функцию Nz (<имя поля>), которая преобразует пустое значение в 0, и только потом суммируйте.