6.2.12. Выполнение агрегирующих операторов

Термина «обобщенный» или «агрегирующий» оператор в Access нет. Есть просто понятие «встроенные функции Microsoft Access», a среди них - «статистические функции» и «статистические функции по подмножеству».

Статистические функции - это Sum (сумма), Count (количество записей, возвращаемых запросом), Avg (среднее), Var (дисперсия) и другие, используемые для расчета итоговых значений. Статистиче­ская функция, с помощью которой в запросе обрабатываются значе­ния поля, может быть выбрана в ячейке строки «Групповая опера­ция» в бланке запроса. Первоначально эта строка в бланке запроса отсутствует. Чтобы она появилась, необходимо выбрать позицию «Групповые операции» меню Вид или щелкнуть по кнопке со знаком «» на панели инструментов.

Первым полем, выводимым в ответ, должно быть поле, по кото­рому проводится группировка, а затем - поля, над которыми прово­дятся вычисления. Группировка может осуществляться не только по одному полю, но и по совокупности полей. Все групповые операции, кроме Count, могут выполняться только над числовыми полями.

В строке «Групповая операция» щелчком мыши можно открыть список доступных функций и в нем сделать выбор нужной стати­стической функции для выполняемых над полем вычислений. На рис. 6.25 приведен пример использования групповых операций в запросе (осуществляется подсчет числа сотрудников, работающих на каждой кафедре).

В Access предварительно упорядочивать таблицу по полю (полям), по которому ведется группировка, не обязательно.

В бланке запроса задают также условия отбора, с помощью кото­рых определяются группы, для которых вычисляются итоговые зна­чения, записи, включаемые в вычисления, или результаты, отобража­емые после выполнения расчетов. На рис. 6.26 изображен запрос, в котором условия отбора применены к вычисляемому полю («Выдать список кафедр, на которых работает меньше трех человек»).

Если предположить (а это практически всегда так), что нет ка­федр, на которых не работает ни одного человека, то результат запро­са будет верен и если задано внутреннее соединение, и если задано левое соединение. Но, предположим, что задается аналогичный по существу запрос «Выдать список сотрудников, имеющих меньше двух детей» на двух связанных таблицах: «Сотрудники» и «Дети». Всегда есть вероятность, что имеются сотрудники, которые не имеют детей.

Если будет использовано внутреннее соединение (а оно задается по умолчанию), то такие сотрудники не попадут в ответ (т.е. результат ответа будет не соответствовать действительности). На результат зап­роса «Выдать список сотрудников, имеющих больше двух детей» па­раметры объединения таблиц не окажут влияния.

Следует быть внимательными при формулировании запросов, поскольку запросы, на первый взгляд кажущиеся однотипными, мо­гут потребовать разной реализации.

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

6.2.13. Вычисляемые поля

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

Например, можно перемножить значения двух полей: «Количе­ство» и «Цена», хранящихся в БД о поставке продукции, и получить величину «Сумма», которая в БД не хранится; или вычислить дату, отстоящую на три месяца от текущей даты и т.п.

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

Так, для текстовых полей возможно использование оператора «+», который в этом случае воспринимается как конкатенация (соедине­ние) строк.

Предположим, что в таблице «Сотрудник 1» имеются три отдель­ных поля: «Фамилия», «Имя» и «Отчество», а нужно вывести эту ин­формацию в одном столбце. Тогда можно использовать выражение

[Фамилия] + " " + [Имя] + " " + [Отчество].

Следует обратить внимание, что, если хотя бы одно из этих трех полей будет не заполнено (причем безразлично, будет это пустое поле или там будут введены пробелы), вся строка будет пустой. В связи с этим рекомендуется вместо операции «+» использовать операцию «&». В этом случае выражение будет иметь вид

[Сотрудник1]! [Фамилия] & " " & [Сотрудник1]![Имя] & " " &

[Сотрудник 1]! [Отчество].

Для расчетов с использованием формул, определяемых пользова­телем, требуется создать новое вычисляемое поле прямо в бланке зап­роса. Вычисляемое поле создается путем ввода выражения в пустую ячейку поля в бланке запроса (рис. 6.27).

Для получения списка, включающего фамилии и инициалы со­трудников, можно использовать следующее выражение:

Фамилияинициалы: [Сотрудник1]! [Фамилия] & " " &

Left([Сотрудник1]![Имя];1) & "." & " " &

Left ([Сотрудник1]! [Отчество]; 1) & "."

В этом выражении использована функция Left. Второй аргумент этой функции определяет число возвращаемых символов.

Для числовых значений можно использовать любые арифмети­ческие операторы. Например, можно создать поле, в котором значе­ние поля «Цена» умножается на значение поля «Количество», чтобы получить «Сумму к оплате».

Результаты вычислений не обязательно должны отображаться в ответе. Их можно использовать, например, в условиях отбора для определения записей, которые выводятся в ответ, но само вычисляе­мое поле в ответ при этом не выводится. Для того чтобы поле в ответ не выводилось, необходимо для него снять значок «V» в строке «Вы­вод на экран» бланка запроса. Так, на рис. 6.28 изображен запрос: «Выдать список сотрудников, работающих меньше 11 месяцев».