Лабораторная работа № 5 Перекрестные запросы
Теоретические сведения
Перекрестный запрос позволяет представить в форме таблицы информацию, которая обычно выводится в виде диаграммы или графика. Перекрестная таблица, в отличие от обычной таблицы, в которой имена имеют только столбцы, состоит из именованных строк и столбцов, на пересечении которых размещается информация.
При конструировании перекрестного запроса необходимо определить:
заголовки строк – поле, каждое уникальное значение которого образует строку в результате запроса (таких полей может быть несколько);
заголовки столбцов – поле, каждое уникальное значение которого образует столбец в результате запроса (такое поле может быть только одно);
значение – поле, к которому применяется необходимая агрегатная функция и полученное значение размещается на пересечении соответствующей пары строки и столбца (такое поле может быть только одно).
Каждое поле может быть задано выражением, записанным как вручную, так и с помощью конструктора выражений. Рассмотрим процесс конструирования перекрестного запроса на основе таблицы Авто (рис. 25).
Рис. 25. Пример конструирования перекрестного запроса
Запрос можно сформулировать следующим образом: получить сумму продаж автомобилей каждой модели в каждом филиале фирмы с общим количеством проданных автомобилей по каждой модели.
Построить перекрестный запрос можно с помощью мастера. Необходимо запустить процесс создания нового запроса, в окне БД на вкладке Запросы выбрать режим Создать. В открывшемся окне выбрать пункт Перекрестный запрос. На первом шаге мастера выбираем исходную таблицу Авто. На втором шаге выбираем поля для заголовков строк – Модель. На третьем шаге выбираем поле для заголовков столбцов – Филиал. По мере выбора поля в нижней части окна появляются образцы заголовков результирующей таблицы в абстрактной форме. На четвертом шаге (рис. 26) выбираем поле, на основе значений которого будут производиться итоговые значения перекрестной таблицы Продажа. На этом же шаге можно отказаться от включения в запрос итоговых результатов выводимых для каждой строки запроса. По умолчанию мастер включит в список заголовков строк дополнительное поле с итогами, в котором будет вычисляться та же агрегатная функция, что и в основном поле значений. На пятом шаге задаем имя запроса и переходим к конструированию в ручном режиме. В окне конструктора запросов заменим функцию Sum в столбце Итоговое значение строки Перекрестная таблица на функцию Count (количество), а также заменим имя Итоговое значение ЦенаПродаж этого столбца в строке Поле на имя Колич прод авто.
Для получения перекрестной таблицы, отображающей результаты продаж каждой модели по месяцам, необходимо для заголовков столбцов выбрать поле Дата продажи, а для интервала группировки – значение Месяц (рис. 27).
Рис. 26. Выбор поля для вычисления перекрестных результатов
Рис. 27. Перекрестный запрос с группированием по месяцам
Ширина таблицы зависит от количества значений поля, выбранного в качестве заголовков столбцов, поэтому следует выбирать поле с меньшим количеством значений. Если требуется сгруппировать записи по нескольким полям (допускается три уровня группирования), то эти поля надо назначать как заголовки строк. Таблица, отражающая результаты продаж по кварталам показана на рис. 28.
Рис. 28. Перекрестный запрос с двумя уровнями группирования по строкам
Для получения такого результата необходимо изменить группирование по столбцам на поквартальное, добавить в конструкторе запроса поле Филиал на вторую позицию, назначить его Заголовками строк.
Практическая работа
При выполнении лабораторной работы необходимо:
для заданной предметной области построить перекрестный запрос (для включения полей из нескольких таблиц сначала необходимо создать обычный запрос, содержащий все необходимые поля);
составить отчет по лабораторной работе.