🗊Презентация Создание итоговых запросов

Лекция №6 Тема: Создание итоговых запросов. Определение : Запросы, выполняющие вычисления в группах записей, называются итоговыми запросами.

Для создания итогового запроса необходимо добавить строку Групповая операция в бланк QBE запроса выборки в режиме Конструктора Для создания итогового запроса необходимо добавить строку Групповая операция в бланк QBE запроса выборки в режиме Конструктора

Пункт меню Вид, Групповые операции Кнопка Σ (групповые операции) на панели инструментов. Замечание: убрать строку Групповая операция можно теми же командами.

Установки групповых операций

Групповые функции

Замечание 1: Функция Count возвращает количество всех записей с непустыми значениями в поле (т. е. полей, не содержащих значения Null). Замечание 1: Функция Count возвращает количество всех записей с непустыми значениями в поле (т. е. полей, не содержащих значения Null). Замечание 2: Функции Min, Max, Count применимы ко всем типам полей; Sum, Avg, StDev, Var – только к числовым и денежным, а First, Last к текстовым.

Примеры создания итоговых запросов 1. Обобщающие запросы по всем записям. Такого типа запросы создаются с участием групповых функций без использования опции группировка. Пример 1: Вычислить количество выполненных заказов, среднее значение скидки, максимальное и минимальное значение стоимости услуг. Бланк запроса QBE будет выглядеть следующим образом:

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

2. Обобщающие запросы для одной группы записей. При использовании опции группировка записи группируются на основе одинаковых значений поля, где указана данная опция и Access в этом случае выполняет вычисления отдельно для каждой группы. Пример 2: Вычислить количество заказов и суммарную скидку за каждый день. Запрос будет создан по таблице Заказы.

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

3. Обобщающие запросы для нескольких групп записей. Можно произвести расчеты над сгруппированными данными по нескольким полям и из нескольких таблиц. Пример 3_1: Вычислить количество заказов и суммарную стоимость услуг за каждый день каждому клиенту. Бланк запроса QBE будет выглядеть следующим образом:

Поле, используемое для группировки необязательно должно находиться в той же таблице, что и итоговое поле. Поле, используемое для группировки необязательно должно находиться в той же таблице, что и итоговое поле. Пример 3_2: Определить суммарную скидку каждой услуги оказанной каждому клиенту. Для создания запроса нам нужны три таблицы: «Клиенты», «Услуги» и «Заказы». Бланк запроса QBE будет выглядеть следующим образом:

4. Группировка с использованием критериев. Кроме того, что можно группировать записи в обобщающем запросе, существует еще возможность устанавливать ограничения на число записей, которые будут обработаны или отображены. Эти ограничения можно применять к трем типам полей: поле, обработанное установкой Группировка; суммируемое поле; не суммируемое поле.

Пример 4.1: Найти количество заказов и суммарную стоимость услуг только для нескольких получателей заказов. (Ограничение на поле с Группировкой) Пример 4.1: Найти количество заказов и суммарную стоимость услуг только для нескольких получателей заказов. (Ограничение на поле с Группировкой) Бланк запроса QBE будет выглядеть следующим образом:

Пример 4_2: Для каждого клиента подсчитать количество заказов и суммарную стоимость услуг и выбрать клиентов для которых суммарная стоимость услуг больше 100. (Ограничение на суммируемое поле после обработки групповой операцией) Пример 4_2: Для каждого клиента подсчитать количество заказов и суммарную стоимость услуг и выбрать клиентов для которых суммарная стоимость услуг больше 100. (Ограничение на суммируемое поле после обработки групповой операцией) Бланк запроса QBE будет выглядеть следующим образом:

Можно отбрасывать записи перед выполнением расчетов. Другими словами ограничим не число строк после выполнения расчетов, а ограничим число записей над которыми нужно производить расчеты. Мы будем использовать установку Условие. Можно отбрасывать записи перед выполнением расчетов. Другими словами ограничим не число строк после выполнения расчетов, а ограничим число записей над которыми нужно производить расчеты. Мы будем использовать установку Условие. Пример 4_3: Для каждого клиента подсчитать количество заказов и суммарную стоимость услуг только для тех заказов у которых эта стоимость <100. (Ограничение на суммируемое поле до обработки групповой операцией)

Пример 5_1: Найти размах стоимости услуги для каждого клиента. Пример 5_1: Найти размах стоимости услуги для каждого клиента.

Можно выполнять итоговые операции над вычисляемыми полями выборки. Можно выполнять итоговые операции над вычисляемыми полями выборки. Пример 5_2: Определить на какую сумму в $ каждого вида услуги оказано каждому клиенту. Бланк запроса QBE будет выглядеть следующим образом:

Пример 6: Подсчитать количество клиентов и суммарную стоимость услуг за сентябрь месяц. (применение критериев отбора для поля, не обработанного групповой операцией) Пример 6: Подсчитать количество клиентов и суммарную стоимость услуг за сентябрь месяц. (применение критериев отбора для поля, не обработанного групповой операцией) В данном примере также была использована установка Условие.

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

В строке Перекрестная таблица нужно выбрать по крайней мере 3 поля: В строке Перекрестная таблица нужно выбрать по крайней мере 3 поля: заголовки строк (Группировка); заголовки столбцов (Группировка); значения (групповая функция). Опции: заголовки столбцов и Значение указываются только раз!!!.

Создание перекрестного запроса с помощью мастера. Создание перекрестного запроса с помощью мастера. запрос  создать  перекрестный запрос выбрать таблицу или запрос из которых будем выводить поля в запросе; далее выбираем поля в качестве заголовков строк; далее выбираем поля в качестве заголовков столбцов; выбираем поле по которому необходимо провести итоговые вычисления и групповую функцию; на этом же шаге подтверждаем создание итоговых значений для каждой строки задаем имя запроса; готово.

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

Примеры создания перекрестных запросов Пример 7: Предположим, что мы хотим подсчитать количество заказов, заказанных клиентами , за каждый день по каждой услуге. Результат представить в виде перекрестной таблицы. Бланк запроса QBE будет выглядеть следующим образом:

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

Заголовки строк для нескольких таблиц. Возможно дополнительное добавление Заголовки строк. При этом отображаемая информация может быть уточнена. Пример 8: Уточним предыдущий запрос, т.е. посчитаем, сколько заказов получено в тот или иной день по каждой услуге от каждого клиента. Бланк запроса QBE будет выглядеть следующим образом:

Условия отбора в перекрестном запросе. Условия отбора в перекрестном запросе. Условия могут устанавливаться для следующих типов полей: для любого нового поля; для поля заголовки строк; для поля заголовки столбцов.

Пример 9: Уточним предыдущий пример условием по новому полю, т.е. посчитаем только те заказы стоимость услуг которых > 10000. Пример 9: Уточним предыдущий пример условием по новому полю, т.е. посчитаем только те заказы стоимость услуг которых > 10000. Бланк запроса QBE будет выглядеть следующим образом:

Пример 10: Определим сколько заказов за определенный месяц заказывал каждый клиент из каждого города. Пример 10: Определим сколько заказов за определенный месяц заказывал каждый клиент из каждого города. В этом случае условие применим для поля заголовки строк. Бланк запроса QBE будет выглядеть следующим образом:

Пример 11: Определим сколько заказов каждый день заказывал каждый клиент из определенного города (например Бреста или Минска). Пример 11: Определим сколько заказов каждый день заказывал каждый клиент из определенного города (например Бреста или Минска). В этом случае условие применим для поля заголовки столбцов. Бланк запроса QBE будет выглядеть следующим образом: