Перекрестные запросы предназначены для получения итоговых расчетов в удобном для анализа виде. Они позволяют на основе реляционных таблиц базы данных формировать таблицы особого вида, в которых присутствуют заголовки не только столбцов, но и строк, составленные из значений полей исходной таблицы, а в ячейках располагаются соответствующие итоговые значения (аналог сводных таблиц в Excel).
Перекрестный запрос можно построить как с помощью Мастера, так и вручную, в режиме конструктора.
þ Мастер строит перекрестные запросы только на основе одной таблицы БД. Если данные берутся из нескольких связанных таблиц, то можно: либо сформировать перекрестный запрос вручную, либо предварительно построить простой запрос на выборку данных из нескольких таблиц, а затем, взяв его в качестве источника данных, использовать Мастер.
5.1. Перекрестный запрос с одной таблицей
Пример 12. Определить среднюю зарплату по каждой должности в каждом отделе.
В рамках обычного запроса на выборку с группировкой можно вычислить среднюю зарплату либо по каждому отделу, либо по каждой должности, но не то и другое вместе. Перекрестный запрос позволяет это сделать.
Выполните следующие шаги:
1. Панель Запросы → Создать → Перекрестный запрос. Заработает Мастер.
2. В первом окне Мастера «Создание перекрестных таблиц» необходимо выбрать источник данных; выберите таблицу «МоиСотрудники», нажмите кнопку Далее.
3. Во втором окне нужно выбрать поле этой таблицы, значения которого будут использованы в качестве заголовков строк. В нашем случае выбрать нужно либо поле Отдел, либо поле Должность. Для более наглядного представления итогов на экране в качестве заголовков строк лучше выбрать поле, у которого больше разных значений, т.е. Должность. Выделите это поле слева и нажмите кнопку со стрелкой для его переноса в правую область окна. Нажмите Далее.
4. В третьем окне выбираем поле, значения которого будут служить заголовками столбцов перекрестной таблицы. Выберите Отдел, Далее.
5. В четвертом окне выбираем поле, по которому подводится итог (проводятся вычисления), у нас – это Начислено. Выберите это поле мышью. Справа в этом же окне появится список итоговых функций, которые можно использовать для этого поля. Выберите Среднее, нажмите Далее.
6. В последнем окне задайте имя полученного запроса или оставьте предлагаемое. Переключатель Просмотреть результаты запроса должен быть включен, если запрос требуется выполнить. Нажмите Готово.
Для тренировки попробуйте создать аналогичный запрос Конструктором, определив максимальную зарплату в каждом отделе по каждой должности. Выполните для этого действия:
1. Панель Запросы → Создать → Конструктор, выберите в бланк таблицу «МоиСотрудники».
2. Выберите поля - участники запроса: Отдел, Должность и Начислено.
3. Меню Запрос → Перекрестный, в бланке появятся строки «Групповая операция» и «Перекрестная таблица».
4. В строке Перекрестная таблица для поля Отдел выберите из списка Заголовки столбцов, для поля Должность – Заголовки строк, для поля Начислено – Значение.
5. В строке Групповая операция для поля Начислено выберите Max.
6. Выполните запрос.
Перекрестный запрос с несколькими таблицами
Пример 13. Определить месячные объемы продаж в 1997 г. по каждому виду товаров, используя таблицы БД «Борей».
Для выполнения задачи потребуются таблицы «Товары», содержащая коды и марки товаров и «Заказы», содержащая даты размещения заказов; но поскольку эти две таблицы связаны через посредника – таблицу «Заказано», то в бланк запроса необходимо включить и ее. Кроме того, эта таблица потребуется для вычисления объема заказов, т.к. содержит цены и заказанное количество товаров.
Шаги выполнения:
1. Создайте запрос Конструктором, выбрав в бланк указанные три таблицы. Из таблицы «Товары» включите в бланк поле КодТовара и поле Марка. Из таблицы «Заказы» - поле ДатаРазмещения.
2. Меню Запрос → Перекрестный. В бланке появятся две новые строки.
3. В столбце поля КодТовара выберите в строке Перекрестная таблица элемент списка Заголовки строк. Для поля Марка сделайте то же самое.
4. В столбце поля ДатаРазмещения в строке Групповая операция выберите элемент списка – Условие. В строке Условие отбора введите условие: Like ”*.1997”.
5. Объем продаж по каждой дате можно определить, умножив цену на количество товара в заказе. Чтобы узнать объем по каждому месяцу полученные значения нужно просуммировать. Поэтому введите в последний (пустой) столбец бланка в строку Поле новое название и выражение:
Объем продаж: Sum([Заказано]![Цена]*[Заказано]![Количество])
В строке Групповая операция для этого поля выберите из списка Выражение, а в строке Перекрестная таблица выберите Значение.
6. В качестве столбцов формируемой перекрестной таблицы должны фигурировать месяцы 1997 года, т.е. дата размещения заказов должна быть выдана в специальном формате, например, «mmm», задающем названия месяцев в трехбуквенном сокращении. Для этого в последний (пустой) столбец введите в строку Поле новое название и выражение:
Месяцы: Format([ДатаРазмещения]; ”mmm”)
В строке Перекрестная таблица для этого поля выберите Заголовки столбцов.
7. Выполните запрос. Обратите внимание, что результирующая таблица содержит названия месяцев, расположенные по алфавиту, а не в естественном порядке. Чтобы исправить эту ситуацию, можно в режиме Конструктора войти в окно Свойства запроса (щелкнув мышью дважды на пустом полотне бланка рядом с таблицами) и в строке Заголовки столбцов этого окна набрать вручную названия месяцев через точку с запятой
8. Выполните скорректированный запрос.
Задания
35. С помощью перекрестного запроса определите, сколько человек работает по каждой должности у каждого заведующего отделом.
36. Постройте перекрестный запрос, используя таблицы БД «Борей», определив средний объем продаж по каждой категории товаров (из таблицы «Типы») по всем кварталам 1998 г.
37. Постройте перекрестный запрос, используя таблицы БД «Борей»: для каждого клиента определить максимальное количество заказанных товаров по каждой категории товаров. Сконструируйте запрос двумя способами: вручную - с помощью Конструктора, и Мастером, создав предварительно многотабличный запрос на выборку нужных полей и взяв этот запрос для Мастера в качестве единственного источника данных перекрестного запроса.
