Создание перекрестного запроса с помощью мастера

 

Легче всего построить перекрестный запрос с помощью мастера создания перекрестного запроса. Если вы хотите сделать это самостоятельно, выполните следующие действия, пользуясь БД AdventureWorks.

1. Если нужно собрать информацию из связанных таблиц, начните с создания запроса с объединением (join query).

В данном примере используется уже созданный запрос Orderedltem с объединением таблиц, заимствующий массу данных о компонентах заказов, соответствующих товарах, клиентах, месте их проживания и т. д. Дополнительные сведения, необходимые для самостоятельного создания запроса с объединением, см. в разд. "Запросы и связанные таблицы" главы 6.

Если вы считаете, что можете получить все необходимое из одной таблицы, пропустите этот пункт.

2. Выберите на ленте Создание > Другие > Мастер запросов (Create > Other > Query Wizard).

Теперь начинает действовать чудесный мастер. На экране появляется окно Создание запроса (New Query) со списком запросов разных типов, которые может создать мастер.

3. Выберите Перекрестный запрос (Crosstab Query Wizard) и щелкните мышью кнопку ОК.

Сначала мастер попросит выбрать таблицу или запрос (рис. 9.4). Выберите один из переключателей в области Показать (View).

4. Выберите нужную таблицу. Если хотите выбрать запрос, щелкните кнопкой мыши переключатель Запросы, а затем выберите ваш запрос. Щелкните мышью кнопку Далее.

В данном примере следует щелкнуть мышью переключатель Запросы и затем выбрать запрос OrderedItems.

На следующем этапе нужно задать критерии группировки, которые будут применяться для объединения данных в строки (рис. 9.5).

 

Рис. 9.4. Для просмотра таблиц вашей БД щелкните кнопкой мыши переключатель Таблицы, а для просмотра запросов — Запросы

Рис. 9.5. Для применения поля в качестве заголовка строки выберите его в списке Доступные поля и затем щелкните мышью забавную кнопку > для переноса поля в список Выбранные поля

 

Если создается простой двухуровневый перекрестный запрос, выберите один критерий для строк и один для столбцов (на следующем шаге). Но есть возможность задать до трех уровней группировки строк. Этот вариант больше всего подходит для разных уровней, связанных между собой. Например, вы можете выбрать группировку по стране проживания клиента, в каждой стране подгруппу для городов и в каждом городе подгруппу на основе идентификационного номера (ГО) клиента. Пример хорошо сгруппированного перекрестного запроса см. на рис. 9.2.

5. Включите поля, которые хотите использовать, в список Выбранные поля и затем щелкните мышью кнопку Далее.

В примере с запросом Orderedltems строки группируются по полю State Province. После того как вы опробуете свой запрос, группировку можно легко изменить в окне Конструктора. Например, если хотите, можно поменять поле StateProvince на поле Country. Как изменить перекрестный запрос, рассказывается в разд. "Создание перекрестного запроса с нуля " далее в этой главе.

Теперь следует задать критерий группировки, применяемый для разделения ваших строк на столбцы (рис. 9.6). В этот момент можно выбрать одно поле.

Рис. 9.6. По мере выполнения мастера программа Access выводит в нижней части его окна мини-окно предварительного просмотра структуры вашего находящегося в процессе создания перекрестного запроса. В данном примере строки группируются по полю StateProvince, а столбцы — по полю ProductCategory

6.  Выберите поле для группировки столбцов и щелкните мышью кнопку Далее. В данном примере это поле ProductCategory.

На последнем шаге вы должны подобрать вычисление, которое хотите выполнять для получения итогов.

 

Выберите поле для вычисления и затем функцию для подсчета сводных данных (рис. 9.7). Например, можно найти самую дешевую продажу, заказ с наибольшим числом проданных товаров, среднюю цену товара и т. д. В данном примере для подсчета количества проданных товаров используется поле OrderQty.

Рис. 9.7. В этом примере функция Sum суммирует значения поля OrderQty из всех записей. Например, данный запрос сообщает о том, что вы продали в целом 53 товара из категории Bike (велосипеды) клиентам из Алабамы. Если нужно посчитать, сколько заказов сделали ваши клиенты (вместо количества доставленных товаров), необходим немного другой запрос — в этом случае следует использовать функцию Count для подсчета различных значений поля SalesOrderID

 

 

Правильный выбор групп

 

Пытаетесь решить, какое поле использовать для группировки  в строках, а какое для группировки в столбцах? Если эти два поля независимы (а они должны быть таковыми), есть смысл применить поле, создающее меньше всего групп для группировки в столбцах. Таблицы с множеством строк и несколькими столбцами легче читать (и печатать), чем таблицы с множеством столбцов и несколькими строками.

Например, если группировать по названию товара и стране, можно биться об заклад, что вы в результате получите больше групп товаров, чем стран. (У вас могут быть клиенты в восьми разных странах, а каталог товаров с 480 видами товаров.) Итак, примените группировку по товарам для строк, а по странам — для столбцов.

8.   Если нужно показать промежуточный итог для каждой строки, установите флажок Вычислить итоговое значение для каждой строки? Да (Yes, include row sums).

 

Промежуточный итог по строке отображается в самом первом столбце. Например, если установлен этот флажок в запросе со штатами и категориями товаров, общие объемы продаж для каждого штата отображаются в первом столбце, за которым следует разбиение продаж по категориям (рис. 9.8).

Рис. 9.8. Заключительный перекрестный запрос показывает связь между штатами и типами товаров, покупаемыми его жителями (слева дается итог для каждого штата или провинции по всем категориям товаров)

9.  Щелкните мышью кнопку Далее.

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

10                 Щелкните мышью кнопку Готово.

 

 

Создание перекрестного запроса с нуля

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

1. Выберите на ленте Создание > Другие > Конструктор запросов (Create > Other > Query Design). Программа Access создаст новый пустой запрос и откроет его в окне Конструктора.

 

2. С помощью окна Добавление таблицы (Show Table) добавьте таблицу или запрос, которые вы хотите использовать, и щелкните мышью кнопку Закрыть.

Если вы используете БД AdventureWorks, легче всего выбрать вкладку Запросы (Queries) в окне Добавление таблицы и добавить запрос Orderedltems.

 

 

Подсказка

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

 

 

3. Выберите на ленте Работа с запросами | Конструктор > Тип запроса > Тип запроса: перекрестный (Query Tools | Design > Query Type > Crosstab),

Программа Access преобразует ваш запрос в перекрестный. Перекрестные запросы выглядят как итоговые с одной лишь разницей. В списке полей в нижней части окна вы найдете дополнительную строку — Перекрестная таблица (Crosstab) (рис. 9.9).

Рис. 9.9. Как и у итоговых запросов, у перекрестных есть свойство Групповая операция, в котором задается способ применения поля: для группировки, фильтрации или вычисления итога. В отличие от итоговых запросов перекрестные включают также свойство Перекрестная таблица, в котором задается размещение полей в строке, в столбце, в виде значений или полное скрытие (в этом случае вы, вероятно, применяете поле для сортировки или фильтрации)

 

 

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

?      Поле используется для группировки по строкам. В данном случае задайте в свойстве Групповая операция значение Группировка и значение Заголовки строк (Row Heading) в свойстве Перекрестная таблица.

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

?      Поле применяется для группировки по столбцам. В этом случае задайте в свойстве Групповая операция значение Группировка и значение Заголовки столбцов (Column Heading) в свойстве Перекрестная таблица.

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

 

Поле отображается как значение в таблице. В этом случае задайте в свойстве Групповая операция итоговую функцию, которую хотите использовать (такую как Sum, Count, Avg и т. д.), и вариант Значение (Value) в свойстве Перекрестная таблица.

Для этой цели вы должны использовать только одно поле. Но вы можете применить выражение, выполняющее вычисления, базирующиеся на значениях нескольких полей. Например, перекрестные запросы, показанные на рис. 9.1 и 9.2, используют выражение Revenue: [UnitPrice] * [OrderQty] для вычисления общей выручки для каждой строки заказа.

 

 

Подсказка

Возможно, вы помните, что мастер создания перекрестного запроса предоставляет возможность показать итог для каждой строки в отдельном столбце. На рис. 9.10 показано, как создать подобный эффект самостоятельно.

 

Рис. 9.10. Обратите внимание на то, что поле OrderQty появляется дважды. В первый раз оно определено как значение, отображаемое в сетке таблицы. Во второй раз оно определяется как заголовок строки, которая создается в дополнительном столбце с итогом для каждой строки. С помощью псевдонима дополнительный столбец переименован в Total Of OrderQty (общее количество в заказе) во избежание путаницы

 

? Поле применяется для фильтрации или отбора. В этом случае задайте в свойстве Групповая операция значение Условие (Where) и вариант (не отображается) (not shown) в свойстве Перекрестная таблица. Затем вставьте условие в поле Условие отбора. (См. выражения для условий отбора в разд. "Построение условий отбора" главы 6.)

 

 

Примечание

К сожалению, в вычисляемом поле нельзя применить фильтрацию или сортировку. Это означает, что если создается запрос, подсчитывающий количество продаж, нельзя отобрать только строки с большими количествами продаж. Но этот трюк можно проделать в сводной таблице, как описано в следующем разделе.

 

 

На рис. 9.10 показано определение запроса, подобного созданному с помощью мастера в предыдущем разделе (см. рис. 9.8).

 

 

Вы можете следить за любыми ответами на эту запись через RSS 2.0 ленту. Вы можете оставить ответ, или trackback с вашего собственного сайта.

Оставьте отзыв

XHTML: Вы можете использовать следующие теги: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

 
Rambler's Top100