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

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

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

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

Термины

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

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

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

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

SQL оператор

можно и так — без промежуточного поля

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

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

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

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

SQL оператор

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

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

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

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

Еще одним простым и быстрым способом создания отчетов является использование Мастера отчетов. С его помощью можно создавать отчеты (от самых простых до включающих подчиненные отчеты), содержащие поля из одной или более таблиц или запросов. Использование Мастера отчетов позволяет очень быстро познакомиться со многими основными средствами разработки отчетов, предоставляемыми Access 2002. Процесс создания отчетов, более сложных, чем позволяет мастер, описан в гл. 10.

Чтобы более наглядно представить процесс создания отчета с помощью мастера, опишем процедуру создания отчета на основе сразу нескольких таблиц: «Товары» (Products), «Поставщики» (Suppliers) и «Типы» (Categories) базы данных «Борей» (Northwind). В отчете будет представлено текущее количество товаров на складе по категориям.

Чтобы создать простой отчет:

1. На панели объектов окна базы данных щелкните по ярлыку Отчеты (Reports) и нажмите кнопку Создать (New). Появится диалоговое окно Новый отчет (New Report) (см. рис. 6.1).
2. В списке диалогового окна Новый отчет (New Report) выделите элемент Мастер отчетов (Report Wizard). В поле со списком, находящемся в нижней части диалогового окна Новый отчет (New Report), содержатся имена всех таблиц и запросов базы данных, которые могут быть использованы в качестве источника данных для отчета. Раскройте этот список и выделите в нем таблицу «Товары». Нажмите кнопку ОК. Появится первое диалоговое окно Мастера отчетов (рис. 6.3).

Рис. 6.3. Первое диалоговое окно Мастера отчетов

3. Прежде всего нужно определить, какие поля включить в отчет. Из таблицы «Товары» в отчете будут использоваться следующие поля: «КодТовара» (ProductID), «Марка» (ProductName), «Цена» (UnitPrice), «НаСкладе» (UnitsInStock). С помощью кнопки «>» (или дважды щелкнув по элементу списка) поочередно переместите эти поля из списка Доступные поля (Available Fields) в список Выбранные поля (Selected Fields). В отчете поля появляются слева направо, в соответствии с последовательностью их расположения в списке Выбранные поля.

Замечание

Если на каком-либо шаге создания отчета при помощи Мастера отчетов была допущена ошибка или возникла необходимость изменения каких-либо параметров, то для возвращения к предыдущему шагу нажмите кнопку Назад (Back). Кроме того, в любой момент можно нажать кнопку Отмена (Cancel) для отказа от создания отчета и возвращения к окну базы данных.

4. Кроме того, чтобы видеть поставщиков товара и категории, в создаваемый отчет необходимо добавить поле «Название» (CompanyName) из таблицы «Поставщики» (Suppliers) и «Категория» (CategoryName) из таблицы «Типы» (Categories). Для добавления первого недостающего поля раскройте список Таблицы и запросы (Tables/Queries) и выберите элемент Таблица: Поставщики (Table: Suppliers). Имя поставщика не должно отображаться в конце отчета. Чтобы столбец «Название» выводился сразу за столбцом «Марка», в списке Выбранные поля (Selected Fields) выделите поле «Марка», затем в списке Доступные поля (Available Fields) выделите поле «Название» и нажмите кнопку «>». Access переместит поле «Название» из списка Доступные поля (Available Fields) и вставит его в список Выбранные поля (Selected Fields) после поля «Марка», перед полем «Цена».
5. Аналогично добавьте в конец списка поле «Категория» из таблицы «Типы». Чтобы перейти ко второму диалоговому окну Мастера отчетов, изображенному на рис. 6.4, нажмите кнопку Далее.

Замечание

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

Рис. 6.4. Второе диалоговое окно Мастера отчетов

6. Во втором диалоговом окне необходимо указать, каким образом будут структурированы данные в отчете. Мастер проверяет связи между таблицами и в зависимости от выбранного представления предлагает несколько вариантов группировки записи в отчете. Чтобы осуществить группировку по товарам, выделите в списке элемент Товары (by Products).
7. Нажмите на кнопку Вывести дополнительные сведения (Show Me More Information) в левой части диалогового окна для отображения одного из вспомогательных диалоговых окон Мастера отчетов. Если в этом диалоговом окне нажать кнопку Показать примеры (Show Me Examples), то Access выведет дополнительные вспомогательные диалоговые окна, в которых используются «примеры из таблиц «Продажи» (Sales Reps), «Клиенты» (Customers) и «Заказы» (Orders), позволяющие продемонстрировать различные способы группировки, которые Мастер отчетов может автоматически применить к создаваемому отчету. Закройте все вспомогательные диалоговые окна и вернитесь к диалоговому окну Мастера отчетов, изображенному на рис. 6.4. Нажмите кнопку Далее (Next) для перехода к третьему диалоговому окну Мастера отчетов.

Рис. 6.5. Предупреждение Мастера отчетов

8. В третьем диалоговом окне Мастера отчетов необходимо определить дополнительные уровни группировки в отчете. Выделите в списке поле «Категория» (Category) и нажмите кнопку «>» (или просто дважды щелкните по этому полю в списке), чтобы установить группировку по категории товаров (рис. 6.6).

Замечание

Если потребуется отменить добавление поля группировки, нажмите кнопку «

Создание запросов для БД Подписка

Задание

Для БД Подписка создать следующие запросы:

1. Общая сумма выручки
2. Количество изданий, которые выписал каждый из подписчиков
3. Количество подписчиков каждого издания
4. Самое популярное издание с количеством подписчиков
5. Самое непопулярное издание с количеством подписчиков
6. Подписчик с максимальным количеством выписанных изданий
7. Сколько заплатил каждый из подписчиков

Выполнение

Рекомендация.

Запрос 1.Общая сумма выручки следует создавать в самую последнюю очередь. Замечание.

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

* * *

Создание запроса «2 Количество изданий которые выписал каждый из подписчиков» (в названии запроса не используйте точки и запятые):

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

2.1. В качестве исходных данных добавим в поле Запроса (Контекстное меню Добавить Таблицу…) Таблицы Подписчики и Подписка

2.2. Выберем 2 поля: Фамилия из Таблицы Подписчики и Код издания из Таблицы Подписка

2.3. Добавим опцию Групповые операции

2.4. Для поля Фамилия оставим операцию Группировка (то есть объединение всех одинаковых записей в одну), а для поля Код издания выберем операцию Count (Количество значений).

Выполним запрос (Перейдем в режим Таблицы или нажмем иконку «Красный восклицательный знак«).

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

* * *

Создание запроса «3 Количество подписчиков каждого издания «:

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

3.1. В качестве исходных данных добавим в поле Запроса (Контекстное меню Добавить Таблицу…) Таблицы Издания и Подписка

3.2. Выберем 2 поля: Название издания из Таблицы Издания и Код подписчика из Таблицы Подписка

3.3. Добавим опцию Групповые операции

3.4. Для поля Название издания оставим операцию Группировка (То есть объединение всех одинаковых записей в одну), а для поля Код подписчика выберем операцию Count (Количество значений).

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

* * *

Создание запроса «4 Самое популярное издание с количеством подписчиков»

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

4.1. Создадим вспомогательный запрос,– 4 1 Максимальное количество подписчиков для изданий, который в результате выдает только одно число – количество подписчиков у самого популярного издания.

Для этого добавим в поле Запроса (Контекстное меню Добавить Таблицу…) Запрос 3 Количество подписчиков каждого издания «, выберем только одно поле Count-Код подписчика (Количество подписчиков), добавим Групповые операции и выберем Max (Максимальное значение).

4.2. В качестве исходных данных для основного запроса выберем опять же запрос 3 Количество подписчиков каждого издания и только что созданный вспомогательный запрос 4 1 Максимальное количество подписчиков для изданий.

4.3. Выберем оба поля запроса 3 Количество подписчиков каждого издания – Названия издания и Count-Код подписчика (Количество подписчиков)

4.4. Для поля Count-Код подписчика (Количество подписчиков) с помощью Построителя выражений в ячейке Условие отбора введем условие – равенства максимальному количеству подписчиков для изданий:

 = !.

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

* * *

Создание запроса «5 Самое непопулярное издание с количеством подписчиков»

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

5.1. Данный запрос может быть получен аналогично предыдущему, с той лишь разницей, что в качестве вспомогательного будет использован запрос 5 1 Минимальное количество подписчиков для изданий, который в результате выдает только одно число – количество подписчиков у самого непопулярного издания.

Для этого добавим в поле Запроса (Контекстное меню Добавить Таблицу…) Запрос 3 Количество подписчиков каждого издания «, выберем только одно поле Count-Код подписчика (Количество подписчиков), добавим Групповые операции и выберем Min (Минимальное значение).

* * *

Создание запроса «6 Подписчик с максимальным количеством выписанных изданий»

В результате необходимо получить таблицу из двух полей, в первом из которых упомянута фамилия одного (или нескольких, если у двух или нескольких подписчиков одинаковое количество выписанных изданий и оно максимальное), а во втором – указано его количество выписанных изданий.

 6.1. Создадим вспомогательный запрос,– 6 1 Максимальное количество выписанных одним подписчиком изданий, который в результате выдает только одно число – количество изданий, которое выписал самый «читающий» подписчик.

Для этого добавим в поле Запроса (Контекстное меню Добавить Таблицу…) Запрос 2 Количество изданий которые выписал каждый из подписчиков, выберем только одно поле Count-Код издания (Количество изданий), добавим Групповые операции и выберем Max (Максимальное значение).

6.2. В качестве исходных данных для основного запроса выберем опять же запрос 2 Количество изданий которые выписал каждый из подписчиков и только что созданный вспомогательный запрос 6 1 Максимальное количество выписанных одним подписчиком изданий.

6.3. Выберем оба поля запроса 2 Количество изданий которые выписал каждый из подписчиков – Фамилия и Count-Код издания (Количество изданий)

6.4. Для поля Count Count-Код издания (Количество изданий) с помощью Построителя выражений в ячейке Условие отбора введем условие – равенства максимальному количеству выписанных одним подписчиком изданий:

 =  !

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

* * *

Создание запроса «7 Сколько заплатил каждый из подписчиков «:

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

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

7.1. В качестве исходных данных добавим в поле Запроса (Контекстное меню Добавить Таблицу…) все три Таблицы: Издания, Подписчики и Подписка

7.2. Выберем поле: Фамилия из Таблицы Подписчики

7.3. В качестве второго поля с помощью Построителя выражений построим произведение – Срок подписки (в месяцах) из таблицы Подписка умножить на Цена подписки на месяц из Таблицы Издания:

 Выражение1: !*!

 7.4. Добавим опцию Групповые операции

7.5. Для поля Фамилия оставим операцию Группировка (то есть объединение всех одинаковых записей в одну), а для поля Выражение1 выберем операцию Sum (Сумма).

Выполним запрос. В результате в левой колонке будут приведены фамилии подписчиков, а в правой будут выведены суммы выплаченных ими средств.

* * *

Создание запроса «1 Общая сумма выручки»

В результате необходимо получить только одно число – сумму денег, выплаченных всеми подписчиками.

8.1. В качестве исходных данных добавим в поле Запроса (Контекстное меню Добавить Таблицу…) Запрос 7 Сколько заплатил каждый из подписчиков

8.2. Выберем единственное поле Выражение1 (Сумму выплаченных каждым из подписчиков средств)

8.3. Добавим опцию Групповые операции

8.4. Для поля Выражение1 (Сумма выплаченных каждым из подписчиков средств) выберем операцию Sum(Суммирование).

Выполним запрос. В результате получим число – общую сумму выручки.

Microsoft Access представляет собой систему управления базами данных. В этой программе вы можете создать различные запросы для отбора данных по определенным критериям, в том числе и вычисляемые.

Вам понадобится

• — компьютер;
• — программа Access.

Инструкция

Выполните добавление вычисляемых полей для того, чтобы сделать расчет в Access. Создать такое поле вы можете в форме, запросе или отчете, по необходимости. Чтобы выполнить подсчет в вычисляемом поле введите выражение. Оно представляет собой формулу, которая аналогична формулам в Excel, только в ней используются не ссылки на ячейки, а имена таблиц и полей.Используйте при построении выражений элементы: идентификаторы (имя поля, заключенное в квадратные скобки, например, поле «Цена» из таблицы «Товары» – ; операторы (+, -, *, /); функции, константы, значения (численные).Создайте вычисляемый запрос, для этого перейдите во вкладку «Запросы» базы данных, выберите «Создать» – «В режиме конструктора». Выберите используемые в расчетах поля нужных таблиц или запросов. В новом поле введите в имя поля выражение, например, =*.Если в запросе вы используете поля одной таблицы, тогда в выражении необязательно указывать ее имя. Если же в нем учувствуют несколько таблиц, тогда к шимени поля добавляйте название таблицы, как приведено во втором шаге. После этого запустите запрос на выполнение с помощью восклицательного знака на панели инструментов.Воспользуйтесь построителем выражений для создания сложных вычислений в запросе. Для этого в свободном поле щелкните мышкой в имени, выберите «Построить». Обратите внимание, что в верхней части построителя находится текстовое поле выражения, которое предназначено для записи выражения. Чуть ниже размещается строка с арифметическими операторами. В нижней области размещены три текстовых поля, которые используются для выбора элементов, вставляемых в выражение.Введите выражение вручную либо сформируйте его из готовых функций и операторов. К примеру, выберите таблицы и поля для добавления в запрос, между ними с панели построителя добавляйте арифметические операторы, либо функции из соответствующего раздела. После этого щелкните «ОК». Вычисляемый запрос в Access готов. Оцените статью!