Рано или поздно, но любой разработчик или профессиональный аналитик сталкивается с ситуацией, что проект перерастает возможности Excel и необходимо часть работы переложить с электронной таблицы на реляционную базу данных. В случае MS Office это, понятное дело, - MS Access. В виду этого на моём сайте будут появляться материалы и об Access.

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

1. Подсчёт процентной величины в записях запроса относительно сумманой величины по всему набору данных

2. Подсчёт в запросе какой-либо величины нарастающим итогом

Термины

Доменные агрегаты - это функции, такие как: Dsum, Dcount, Davg, Dmin, Dmax, Dfirst, Dlast, DLookup и некоторые другие. Данные функции производят какую-либо групповую операцию над всем набором данных (доменом) и возвращают результат в виде числа. Например, вызов такой функции DSUM("[Amount]", "Sales") вернёт нам итоговоую сумму по полю Amount в таблице Sales.

Подсчёт процентной величины

В этом примере мы подсчитываем сумму проданного товара (поле [Count]) по каждому коду продукта (поле [ProductCode]), а также процентную величину, которую занимает сумма по каждому коду относительно суммы по всем кодам продуктов.

Запрос в конструкторе выглядит следующим образом:

SQL оператор

можно и так - без промежуточного поля [SCount]

Обратите внимание, что конструкция DSUM будет возвращать в каждую запись набора одно и тоже число

Подсчёт величины нарастающим итогом

Здесь мы в поле [RT] подсчитываем нарастающим итогом количество заказов с группировкой по дате заказа (поле [OrderDate]). То есть каждый последующий день включает в себя количество заказов за эту дату плюс все предыдущие даты.

Запрос в конструкторе:

SQL оператор

Комментарии:

1. Обратите внимание на то, как офомлен третий параметр в функции Dcount. Это выстраданный синтаксис, так как функция Dcount работает с датами только в стандартном формате вида #mm/dd/yyyy#.

2. В англоязычных книгах рекомендуют следующий формат DCount("[OrderID]","Orders","[OrderDate] <= #" & [OrderDate] & "#"), но он не работает с нашими региональными настройками, поэтому - только так, как показано.

3. Трюк с нарастающим итогом, как не трудно догадаться, кроется в знаке меньше или равно и динамическим формированием условия выборки.