В предыдущем разделе был рассмотрен запрос, содержащий вычисляемые поля, однако часто бывает необходимо найти суммы, среднее значение, максимальную или минимальную величину в каком-либо поле. Для нахождения этих величин надо создавать итоговый запрос. Итоговые запросы – это запросы выполняющие вычисления в группах записей.
Для создания итогового запроса в окне конструктора запросов надо выполнить команду «Групповые операции» из меню «Вид» или нажать на кнопку «Групповые операции» на панели инструментов. После этого в бланке запроса появляется новая строка «Групповая операция:». Любое поле этой строки это поле списка, который содержит все доступные групповые операции. При щелчке по кнопочке в поле этой строки открывается этот список (рис.4.23). В приведенной ниже таблице описаны все операции из этого списка.
Операция
Выполняемые действия
Группировка
Группирует одинаковые значения данного поля, т.е. повторяющиеся значения будут в результирующей таблице отображаться только один раз.
Sum
Суммирование значений поля
Avg
Нахождение среднего значения
Min
Минимальное значение
Max
Максимальное значение
Count
Количество записей, содержащих значения
StDev
Стандартное отклонение
Var
Дисперсия
First
Значение в первой записи
Last
Значение в последней записи
Условие
Поле «Условие отбора:» бланка запросов обязательно должно быть заполнено.
Предположим нам надо подсчитать, на какую сумму продано товаров каждого наименования каждому покупателю. Для создания такого итогового запроса необходимы данные из таблиц «Покупатели», «Товары» и «Продажи». В бланке запроса в окне конструктора запросов в первый столбец надо ввести фамилию покупателя из таблицы «Покупатели», во второй столбец наименование товара из таблицы «Товары» и в третьем столбце создать вычисляемое поле «Стоимость». Далее необходимо выполнить команду «Групповые операции» и строке «Групповая операция:» бланка запроса установить для полей «Фамилия» и «Товар» операцию Группировка, а для поля «Стоимость» операцию Sum(рис.4.24).
В результирующей таблице данного запроса для каждого покупателя будет создаваться список товаров, которые данный покупатель покупал, и будет производиться подсчет стоимости покупок каждого товара данным покупателем (рис.4.25).
4.6. Перекрестный запрос
Как видно из рис.4.25. результат итогового запроса содержит 190 записей и неудобен для просмотра, т.е. для просмотра всех данных надо листать таблицу. Перекрестный запрос позволяет сделать вывод данных более удобным и информативным, т.е. так, чтобы на экране отображалось одновременно больше информации и в более удобной для восприятия форме.
Для ознакомления с перекрестным запросом создадим его на основе уже созданного запроса «Стоимость по покупателям», бланк которого представлен на рис.4.24. В результате выполнения перекрестного запроса в результирующей таблице должны отображаться те же данные, что и в запросе «Стоимость по покупателям», в другой форме. Для создания перекрестного запроса необходимо:
1. На закладке «Запросы» БД нажать на кнопку «Создать».
2. Выбрать инструмент «Перекрестный запрос» в окне диалога «Новый запрос».
3.
В открывшемся окне диалога «Создание перекрестных таблиц» надо выбрать базу для создания запроса, т.е. таблицу или запрос, на основании которого будет создаваться данный запрос. Здесь надо выбрать в поле «Показать» переключатель «Запросы», выбрать запрос «Стоимость по покупателям» и нажать на кнопку «Далее».
4. На следующем шаге создания перекрестной таблицы надо выбрать поле, значения которого будут размещаться в первом столбце таблицы. Если полей несколько, то они будут размещаться в нескольких первых столбцах.
5.
На следующем шаге надо выбрать поля, значения которых будут располагаться в первой строке, т.е. будут заголовками столбцов (рис.4.27).
6. Дальше необходимо выбрать поля, для которых будет производиться итоговая операция и выбрать нужную операцию (рис.4.28).
7. На последнем шаге надо задать имя запроса.
Результирующая таблица созданного запроса показана на рис.4.29. Как видно из рисунка в данной таблице фамилии покупателей располагаются в первом столбце, а наименования товаров в первой строчке. Результат итоговой операции располагается на пересечении строки и столбца. В таблице представлены также итоговые данные каждого покупателя по всем товарам (второй столбец). Данная таблица представляется более удобной для просмотра данных и более информативной (в ней отображается одновременно отображается больше информации чем в таблице на рис.4.25).
Если при создании перекрестной таблицы есть поля, которые каким-либо образом можно объединять (например, поле «Дата» можно объединять по месяцам, кварталам и т.д.), то после размещения полей перекрестной таблицы по столбцам и строкам мастер создания перекрестной таблицы предложить выбрать интервал для группировки данных. Предположим, что создается перекрестная таблица на основе итогового запроса «Продажа товара по датам», в котором подсчитывается количество проданного товара за каждый день (рис.4.30).
При создании перекрестной таблицы для данного запроса будет предложено выбрать интервал группировки данных по дате (рис.4.31). Если выбрать какой-нибудь из предложенных интервалов (например, месяц), то итоговая операция будет вычисляться и выдаваться в таблице за указанный период. Например, если в мастере перекрестных таблиц выбрать для запроса «Продажа товара по датам» группировку данных по месяцам, то результирующая таблица будет иметь вид, представленный на рис.4.32, т.е. количество проданного товара будет подсчитываться за каждый месяц.
4.7.
Модифицирующие запросы
Все рассмотренные выше запросы были запросами для выбора данных из таблиц и выполнения вычислений над ними. Однако достаточно часто бывает необходимо менять данные в самих таблицах. СУБД Microsoft Access позволяет делать это с помощью запросов. Запросы, которые позволяют менять данные в таблицах БД называются модифицирующими запросами. Существует четыре вида модифицирующих запросов:
1. Запрос создания новой таблицы.
2. Запрос добавления.
3. Запрос удаления.
4. Запрос замены.
Запрос создания таблицы фактически не модифицирует таблицу, а создает новую таблицу. В данном пособии уже рассматривались механизмы для создания новых таблиц, поэтому такие запросы рассматриваться не будут.
