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

Пока вы пользовались запросами для отбора и просмотра данных из таблицы. Но таблица может дать гораздо больше информации. Мы имеем в виду вычисление значений на основании существующих полей. Например, Сведения Заказов содержит поле Количество1, а в таблице Склад есть поле Цена. Количество1 сообщает, как много изделий заказал клиент. Цена – стоимость одного изделия. Для вычисления общей стоимости необходимо перемножить значения этих полей. (Она понадобится также при расчете полной суммы заказа.)

На рис. 5.9 представлен запрос, который может стать основой для отчета о состоянии дел на складе. Он содержит поле Код клиента из таблицы Клиенты, поля Код Заказа и Дата из таблицы Заказы, поле Код изделия из таблицы Сведения заказов. В запросе также участвует таблица Склад (ее не видно, так как она располагается во втором ряду, как раз под списком полей запроса), так что ее поля доступны для вычисления стоимости заказа. Обратите внимание, что вычисляемые поля могут участвовать в сортировке и фильтрации данных.

Рис.5.9. Запрос для подсчета стоимости

 

Чтобы произвести вычисления, в строке Поле (Field) вводится выражение (математическое). В нашем примере в первом пустом столбце введите следующее: Сумма:[Сведения Заказов]![Количество1]*[Цена]. «Сумма» – это название будущего столбца; за ней следует двоеточие. Если не указать заголовка, то Access создаст его сам, причем весьма бессмысленный. Имена полей вводятся в том виде, как они указаны в таблице, только заключенные в квадратные скобки, а для вычислений используются математические операторы.

Если какое-то поле встречается только в одной таблице, то ее имя указывать необязательно. Однако в нашем случае две используемые таблицы содержат поле Количество1. Если бы в выражении использовались только имена полей, то Access выдал бы сообщение о неоднозначной ссылке на поле. Мы не хотим огорчать Access, поэтому указываем имя таблицы, заключенное в квадратные скобки, восклицательный знак, затем имя поля: [Сведения Заказов]![Количество1]. Это гарантирует, что в поисках поля Количество1 программа обратится к таблице Сведения Заказов, а не к таблице Склад.

Выполните запрос и посмотрите на вычисленное значение. (При добавлении нового поля Access автоматически устанавливает флажок.)

 

Установка свойств

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

Чтобы установить свойства поля, щелкните на любой строке столбца этого поля правой кнопкой мыши и выберите во всплывающем меню команду Свойства (Properties). Можно также выбрать поле и щелкнуть на кнопке Свойства (Properties) в панели инструментов. Вы увидите следующее окно диалога:

Рис. 5.10. Свойства поля

Большая часть свойств предназначена для задания дополнительных возможностей. Свойство Маска ввода (Input Mask) определяет формат ввода. Чтобы задать формат отображения, раскройте список Формат (Format) и выберите то, что вам нравится, после чего закройте окно диалога.

 

Групповая обработка данных

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

Для того чтобы добавить эту строку, щелкните мышью на кнопке Групповые операции (Totals), и в списке полей запроса появится новая строка с названием Групповая информация (Total). По умолчанию у всех полей выбрано Группировка (Group By). Если вы желаете вычислить суммарные значения по счетам, то придется выполнить группировку по полям, которые однозначно определяют заказ, а именно Код клиента и Код заказа, так как только они обеспечивают в этом случае уникальность.

 

Рис. 5.11. Запрос на выборку

Так как нас больше не интересуют отдельные товары, удалите поля Дата и Изделие; чтобы выделить столбец, щелкните мышью на серой полоске над ним и нажмите клавишу Del, чтобы удалить его. В полях, не участвующих в группировке, указывается характер вычислений. Например, чтобы вывести итог по каждому счету, щелкните мышью в строке Групповая операция вычисляемого поля, раскройте список и выберите Sum (сумма). Когда вы выполните запрос, Access выведет сумму для каждого заказа.

В списке Групповая операция содержатся и другие операции, которые можно выполнить над содержимым полей. Можно сменить статистическую математическую функцию, использовать Условие (Where) или задать выражение. Для задания условия создания группы используется значение Условие. Например, если вы хотите сгруппировать клиентов Москвы, добавьте в список полей запроса Город, выберите в качестве оператора Условие и введите в строке Условие отбора (Criteria) значение Москва. (При выборе значения Условие Access сам сбросит флажок Вывод на экран.)

Оператор Условие определяет, какие записи будут входить в группы. Можно выбрать также, по каким группам выводить результаты. Например, вы хотите ограничить список только теми заказами, общая сумма которых превышает $500. Для этого в строке Условие отбора для вычисляемого поля нужно задать значение 500.

 

Свойства запроса

Есть и другие способы, позволяющие указать, какие записи или группы надо выводить в запросе. Вы, должно быть, заметили на панели инструментов Запросы поле со значением Все (All). Это поле Набор значений (Top Values). По умолчанию Access выводит все записи, удовлетворяющие поставленным условиям. При сортировке выборки вас, тем не менее, могут интересовать не все записи, а, например, пять наиболее крупных заказов или десять наименее успевающих студентов.

Чтобы ограничить количество выводимых записей, введите нужное число в поле Набор значений или раскройте список и выберите из него одно из следующих значений:

Контролировать вывод данных можно и через свойства запроса. Чтобы увидеть свойства запроса, щелкните мышью на свободном месте в серой области окна запроса, где располагаются окна со списками полей, затем щелкните на кнопке Свойства (или щелкните правой кнопкой мыши и выберите из появившегося меню команду Свойства). Вы увидите следующее окно диалога:

 

Рис. 5.12. Свойства запроса

 

Введите значение в поле Набор значений или выберите его из раскрывающегося списка.

Двумя другими весьма полезными свойствами являются Уникальные значения (Unique Values) и Уникальные записи (Unique Records). Хотя в таблице и не должно быть повторяющихся записей, сочетания используемых в запросе полей могут привести к появлению в выборке повторяющихся строк. Если значение свойства Уникальные значения – Нет (NO), Access будет выводить все записи, даже если значения полей в некоторых из них совпадают. Если выставлено Да (Yes), то повторяющихся строк в выборке не будет.

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

Мы завершили изучение свойств запросов, поэтому вы можете закрыть окно Свойства запросов (Query Properties). Если вы создали рассматривавшийся здесь запрос, то сохраните его с именем Список заказов и закройте окно запроса.