Станьте знатоком статистических функции по подмножеству

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

В программу Access включено восемь статистических функций по подмножеству.

• DSum вычисляет сумму нескольких значений. Ее можно использовать для подсчета общей стоимости заказа.

• DAvg рассчитывает среднее арифметическое нескольких значений. Ее можно применить для расчета средней цены товаров.

• DCount считает количество соответствующих записей. Она используется для вычисления числа элементов в заказе или количества заказов, сделанных клиентом.

• DMin и DMax находят наименьшее или наибольшее значение в подмножестве. Их можно использовать для поиска удешевленных или самых дорогих товаров.

• DFirst и DLast извлекают первое или последнее значение из подмножества. Если отсортировать список заказов по датам, можно найти самый старый и самый свежий заказы.

• DLookup находит значение, удовлетворяющее заданным критериям. Ее можно применять для просмотра таблицы и поиска названия товара с заданным ID.

Все статистические функции по подмножеству принимают три одинаковых параметра. Первый — поле (или вычисляемое выражение), которое хотите извлечь или использо­вать в вычислении. Второй — применяемая таблица или запрос. Третий параметр со­держит любые условия отбора, используемые для сокращения числа строк. Если вы хо­тите найти среднюю цену всех напитков, продаваемых компанией Boutique Fudge, нужно использовать поле Price(в качестве первого параметра), таблицу Products(как второй параметр) и отфильтровать ее, включив в подсчет товары с категорией Beverages (напитки) (третий параметр).

Для вычисления стоимости всех компонентов заказа применяется функция DSum. Нуж­ная вам информация хранится в таблице OrderDetails,но вы хотите отобрать только те записи, у которых поле OrderlDсовпадает с идентификационным номером текущего заказа. Наконец, нужно сложить вместе стоимости всех компонентов заказа. И как вы знаете из ранее изложенного, стоимость в строке заказа вычисляется перемножением полей Priceи Quantity.

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

=DSum("Price*Quantity","OrderDetails","OrderID=" & [ID])

Первый аргумент — вычисляемое поле, которое берется из каждой записи. Второй аргу­мент — имя используемой таблицы. Третий аргумент отбирает только те записи, которые соответствуют текущему заказу. Если у текущего заказа идентификационный номер (ID) 455, последний параметр отберет все записи из таблицы OrderDetails,у которых OrderID=4 55. И снова вы сможете охватить все разом с помощью функции Format, если хотите, чтобы окончательный результат выглядел как денежная сумма.

У данного вычисляемого поля есть одна хитрость, но сначала придется внести еще одно усовершенствование. Обычно программа Access подсчитывает вычисляемые ноля при пер­вом отображении записи. Однако вам нужна гарантия того, что общий итог вычисляется заново при каждом изменении в списке элементов заказа. Для этого необходимо вызывать метод Form.Recalc, когда запись из таблицы OrderDetailsдобавляется, обновляется или удаляется. Далее приведен программный код, реализующий этот прием:

Private Sub Form_Afterlnsert()

Forms("PlaceOrder").Recalc

End Sub

Private Sub Form_AfterUpdate()

Forms("PlaceOrder").Recalc

End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)

Forms("PlaceOrder").Recalc

End Sub

Теперь можно создать и заполнить заказ, не строя догадок о его общей стоимости.

Получение сведений о цене

Как вы узнали из главы 5, иногда в таблице приходится хранить моментальные данные - информацию, которая копируется из одной таблицы в другую, поскольку может меняться со временем. Хороший пример — цены товаров, которые эволюционируют со временем ("эволюционирование" — это мягкий аналог "неуклонного роста"). Итак, у текущего товара необязательно та же цена, по которой вы заказывали его на прошлой неделе. Для того чтобы отслеживать величину вашего долга компании, в таблице OrderDetailsнужно хранить про­дажную цену товара.

Но эта система создает проблему при заполнении заказа. Выбрать компонент заказа до­вольно легко — нужно выделить товар в списке подстановки. Но список подстановки уста­навливает только поле ProductIDдля записи таблицы OrderDetails.Целиком ваша задача — выяснить правильную цену и скопировать ее из таблицы Productsв новую запись.

К счастью, это можно сделать довольно легко. Можно отреагировать на событие On Change (Изменение) в списке ProductID,которое возникает при каждом выборе товара. Затем можно применить статистическую функцию по подмножеству DLookup для поиска соответствующей цены и вставить ее в поле Priceавтоматически. Далее приведен код, де­лающий это:

Private Sub ProductID_Change ( )

Price = DLookup("Price", "Products", "ID=" & ProductID)

Quantity =1

End Sub

В данном коде также задается значение 1 для поля Quantity,что служит важной отправ­ной точкой. Если необходимо, можно изменить значения полей Priceи Quantityпосле того, как товар выбран. Или создать более строгую форму — можно задать значение Да в свойстве, Блокировка (Locked) элемента управления Price,чтобы запретить любые изменения цены 1 (как сделано в БД Boutique Fudge). В этом случае, когда создается заказ, вы вынуждены использовать действующую в данный момент цену без возможности скидок.

Примечание

Этот метод можно применять для заполнения других моментальных или зависящих от времени данных. Можно извлечь адресные данные текущего клиента и использовать их как отправную точку для адреса доставки. Можно даже применить функцию DLookup для создания более сложных процедур проверки на значения. Можно использовать этот способ в БД школы Саcophone Music для поиска предварительных условий и максимального размера класса, прежде чем зачислить студента в данный класс.