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

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

1. Откройте запрос "Итоги по клиентам", который мы построили в предыдущем разделе, в режиме Конструктора.
   
2. Перетащите поле "Страна" (Country) из таблицы "Клиенты" (Customers) в бланк запроса. В строке Групповая операция (Total) для этого поля появляется элемент Группировка (Group by).
   
3. Замените элемент Группировка (Group by) на элемент Условие (Where), выбрав его из списка (рис. 8.23). При этом автоматически сбрасывается флажок (Show) в данном столбце. Это означает, что в результат запроса поле "Страна" не попадет. Если вы попытаетесь установить этот флажок, то при выполнении запроса будет выведено сообщение об ошибке (рис. 8.24). Действительно, если мы хотим, чтобы поле "Страна" отобразилось в результате запроса, его нужно добавить в бланк запроса второй раз, при этом в строке Групповая операция (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, содержащее условие отбора записей.
   

Рис. 8.23. Задание условия отбора записей в запросе "Итоги по клиентам"

Рис. 8.24. Сообщение об ошибке при попытке отобразить поле, для которого задано условие

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

Рис. 8.25. Результаты запроса "Итоги по клиентам по городам"

Статистические функции можно использовать также в вычисляемых полях запроса. Например, для создания запроса "Итоги по клиентам" мы могли бы нместо запроса "Сведения о заказах" использовать прямо таблицу "Заказано". На рис. 8.26 показано вычисляемое поле "Стоимость". В выражение для этого поля включена функция Sum ():

Стоимость: Sum(CCur([Заказано].[Цена]*[Количество]*(1-[Скидка])/100) *100)

Тогда в строке Групповая операция (Total) для этого столбца должен быть выбран элемент Выражение (Expression). При использовании группировки по полю "КодЗаказа", поле "Стоимость" будет содержать общую стоимость заказа.

Можно создать и другое вычисляемое поле:

Стоимость: CCur([Заказано].[Цена]*[Количество]*(1-[Скидка])/100) *100,

а функцию Sum () использовать в строке Групповая операция (Total). Оба варианта будут правильны.

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

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

Внимание

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