Перекрестные
запросы
Перекрестные
запросы —
это запросы, в которых происходит статистическая обработка данных,
результаты которой выводятся в виде таблицы, очень похожей на сводную таблицу
Excel. Перекрестные запросы обладают следующими достоинствами:
-
возможностью обработки
значительного объема данных и вывода их в формате, который очень хорошо подходит
для автоматического создания графиков и диаграмм;
-
простотой и скоростью
разработки сложных запросов с несколькими уровнями детализации.
Однако они
имеют и недостатки — например, нельзя сортировать таблицу результатов по значениям,
содержащимся в столбцах, т. к. в подавляющем большинстве случаев одновременное
упорядочивание данных в столбцах по всем строкам невозможно. При этом вы можете
задать сортировку по возрастанию или по убыванию для заголовков строк.
Перекрестные
запросы удобны для представления данных в виде таблицы, но т. к такая таблица
часто имеет разное количество столбцов, на них довольно сложно строить отчеты.
Создание отчета на базе перекрестного запроса будет рассматриваться
в гл.
13.
В качестве
примера сформируем два перекрестных запроса к базе данных "Борей"
для вывода ежемесячных продаж товара (по его коду) и ежеквартальных продаж товара
(по его типу). Подобные таблицы, содержащие итоги, которые подводятся через
определенные промежутки времени, например через каждый месяц или квартал, обычно
используются в качестве данных для графиков.
В описываемой
ниже пошаговой процедуре создания перекрестного запроса показано, как можно
пользоваться выражениями для создания новых вычисляемых полей в запросах.
Итак, мы будем
создавать перекрестный запрос, в котором в строках выводятся товары, а в столбцах
— соответствующие им ежемесячные объемы продаж. Для этого:
-
Создайте новый запрос
в режиме Конструктора и добавьте в него таблицы "Товары" (Products),
"Заказы" (Orders) и "Заказано" (Order Details).
-
Перетащите поля "КодТовара"
(ProductlD) и "Марка" (ProductName) таблицы "Товары",
а затем поле "ДатаРазмещения" (OrderedDate) таблицы "Заказы"
в первые три столбца бланка запроса.
-
Выберите команду меню
Запрос, Перекрестный
(Query, Crosstab Query). Заголовок окна запроса
Запрос1:на выборку
(Query1:Select Query) изменится на
Запрос!перекрестный
запрос
(Queryl:Crosstab Query). Кроме того, в бланк запроса будут добавлены
строки
Перекрестная таблица
(Crosstab) и
Групповая операция
(Total),
в которую во всех столбцах автоматически вводится операция
Группировка
(Group By).
-
Выберите в списке ячейки
Перекрестная таблица
столбца "КодТовара" значение
Заголовки
строк
(Row Heading). Выполните то же самое для столбца "Марка".
Эти столбцы являются требуемыми заголовками строк перекрестной таблицы.
-
Выберите в списке ячейки
Групповая операция
столбца "ДатаРазмещения" значение
Условие
(Where). В ячейке
Условие отбора
(Criteria) этого столбца введите
выражение <=#31.12.97# And >=#01.01.97# для вывода в перекрестной таблице
данных за 1997 год.
-
Установите курсор в
ячейку
Поле
(Field) следующего (пустого) столбца и введите выражение:
Объем продаж: Sum([Количество]*[Заказано].[Цена])
Это будет поле, значение
которого вычисляется с помощью описанного выражения, а название поля — "Объем
продаж". В выражении используются поля из таблиц, которые включены
в запрос, однако обратите внимание, что сами поля в результат запроса не
включены. Это означает, что при создании вычисляемого поля в выражение можно
включать ссылки не только на поля самого запроса, но и на поля, которые
не включаются в результат запроса. Важно, чтобы они были в исходных таблицах.
При ссылке на поле "Цена" в выражении мы указали еще имя таблицы
"Заказано", а при ссылке на поле "Количество" не указывали.
Указать имя таблицы пришлось потому, что поле с именем "Цена"
присутствует и в таблице "Товары" и в таблице "Заказано".
Если не указать в выражении имени таблицы, Access не сможет определить,
из какой таблицы брать значения, поэтому при выполнении запроса выдаст сообщение
об ошибке, как это представлено на рис. 8.13.
![](12.gif)
Рис. 8.13.
Сообщение об ошибке в выражении вычисляемого поля
Выберите в ячейке
Групповая
операция
(Total) того же столбца значение
Выражение
(Expression),
а затем в ячейке
Перекрестная таблица
— значение
Значение
(Value).
В столбце "Объем продаж" вычисляется общий объем заказов на каждый
товар, который будет подставляться в ячейки результирующей таблицы запроса.
-
Установите курсор в
ячейку
Поле
(Field) следующего (пустого) столбца и введите выражение
Format([ДатаРазмещения];"mmm"). Функция Format О в данном случае
будет возвращать три первые буквы месяца от значения даты в столбце "ДатаРазмещения".
С помощью этой функции мы создали еще одно вычисляемое поле в запросе, причем
после окончания ввода этого выражения Access автоматически создает имя этого
поля "Выражение!" (Exprl). Так происходит всякий раз, когда мы не
определяем имя вычисляемого поля. Выберите из списка в ячейке
Перекрестная
таблица
этого столбца значение
Заголовки столбцов
(Column Heading)
(рис. 8.14). Это означает, что данный столбец запроса будет преобразован в
процессе выполнения запроса в строку, содержащую заголовки столбцов результирующей
таблицы.
![](14.gif)
Рис. 8.14.
Перекрестный запрос в режиме Конструктора
-
Нажмите кнопку
Запуск
(Run). Появится результирующее множество записей, представленное на рис.
8.15.
![](15.gif)
Рис. 8.15.
Результирующее множество перекрестного запроса
Стоит обратить
внимание, что выведенная перекрестная таблица обладает одним недостатком: расположение
столбцов определяется алфавитным порядком их заголовков, а не хронологической
очередностью. Избежать этой неприятности можно. Для этого необходимо задать
порядок сортировки заголовков столбцов. В следующем разделе мы покажем, как
это можно сделать.
Содержание раздела