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

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

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).

 

 

Сводные таблицы

Если итоговые и перекрестные запросы не потрясли вас в достаточной степени, у програм­мы Access есть еще одно мощное средство для подытоживания ваших данных. Сводная таб­лица — это специальная таблица, выполняющая те же трюки, что и перекрестный запрос — группировку по строкам и столбцам — но обладающая большими функциональными воз­можностями. Далее перечислены некоторые из них.

■ Сводные таблицы можно перестроить в любой момент. Быстрым перемещением с помощью мыши вы можете превратить итоги продаж по странам в сетку с продажами в зависимости от возраста клиента. Сводные таблицы — незаменимое средство исследования (добычи) данных, в ходе которого вы пытаетесь выудить скрытые тенденции и связи из массы необработанных сведений.

■ Сводные таблицы поддерживают неограниченное число уровней группировки. Вы не ограничены одним уровнем группировки по столбцам, как в перекрестном запросе. Вместо этого можно разбить строки и столбцы на группы более мелкие.

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

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

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

 

 

Примечание

Многие приверженцы программы Access живут долго и счастливо, даже не сталкиваясь со сводными таблицами. И все потому, что это узкоспециализированное средство, и многие спе­циалисты предпочитают анализировать информацию в других программах (например, в Micro­soft Excel). Но свойства сводных таблиц заслуживают внимания, поскольку могут оказаться полезными в следующий раз, когда вам потребуется сделать общие выводы о производитель­ности вашей компании, выпекающей изделия, посвященные знаменитостям.

Построение сводной таблицы

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

 

 

Примечание

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

 

 

Для применения режима Сводная таблица (Pivot Table) откройте таблицу или запрос, которые хотите использовать, и затем выберите на ленте Главная → Режимы → Режим → Сводная таблица (Home → Views → View → PivotTable View).

Сначала сводная таблица отображается пустой (рис. 9.11).

 

Рис. 9.11. Этот пример отображает запрос Orderedltems в режиме Сводная таблица. Пока еще не на что смотреть, потому что вы не построили сводную таблицу. Справа открыто окно Список полей сводной таблицы, включающее все поля вашей таблицы или запроса

Примечание

Сводные таблицы одновременно работают только с одной таблицей или запросом. Поэтому стоит создать запрос, объединяющий все нужные вам таблицы, так же как вы делали при по­строении перекрестного запроса. Можно также воспользоваться запросом для создания до­полнительных вычисляемых полей (например, поля, перемножающего стоимость товара и ко­личество единиц товара).

 

 

Для создания сводной таблицы нужно сообщить программе Access о том, какие поля ис­пользовать в каждой части таблицы. Все сводные таблицы сформируются из пяти компо­нентов:

■ поля строк применяются для группировки записей в строках;

■ поля столбцов используются для группировки записей в столбцах;

■ поля итогов применяются для вычисления итоговых значений в каждой группе;

■ в полях деталей или подробностей выводятся отдельные значения для каждой записи в группе. При желании вы можете выводить на экран итоговую информацию (в этом случае поле деталей действует как поле итогов);

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

 

 

Примечание

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

 

 

Самый легкий способ освоить сводные таблицы и множество их функциональных воз­можностей — попытаться построить хотя бы одну своими руками. Приведенные далее дей­ствия подробно описывают процесс создания простой сводной таблицы, которая отображает итоги объемов продаж, сгруппированных по странам и категориям товаров. Если вы хотите действовать в соответствии с ними, используйте запрос OrderedItems в БД AdventureWorks, которую можно загрузить с Web-страницы "Missing CD" на сайте www.missingmanuals.com. Затем вы сможете усовершенствовать сводную таблицу, вос­пользовавшись ее богатыми возможностями.

 

Примечание

Предпочитаете визуальный подход к изучению сводных таблиц? На странице "Missing CD" можно найти и изобразительный ряд, интерактивное анимационное руководство.

 

 

1. Из окна Список полей сводной таблицы перетащите с помощью мыши поле РгоductCategory в область Перетащите сюда поля строк (Drop Row Fields Here). Когда вы переместите поле, программа Access выведет все категории товаров сверху до­низу в алфавитном порядке (рис. 9.12). Если нужен обратный порядок сортировки, про­сто щелкните правой кнопкой мыши одно из значений и выберите последовательно Сор­тировка → Сортировка по убыванию (Sort →Sort Descending).

 

Подсказка

Если вы случайно закрыли окно со списком полей сводной таблицы, его можно вернуть выбрав на ленте Работа со сводными таблицами I Конструктор -> Показать или скрыть -» Список полей (PivotTable Tools I Design -> Show/Hide -> Field List).

 

Рис. 9.12. В данном примере в область строк добавлен список товаров, а элементы второй группировки (список стран) переносится в область столбцов. Обратите внимание, как только поле выбрано, его имя отображается жирным шрифтом в окне Список полей сводной таблицы

 

2. Из списка полей сводной таблицы перетащите поле Country в область Перетащите сюда поля столбцов (Drop Column Fields Here).

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

 

 

Подсказка

Если перемещение с помощью мыши утомительно, есть другой способ создания макета свод­ной таблицы. В окне Список полей сводной таблицы просто выберите поле, которое хотите добавить в сводную таблицу, и затем в раскрывающемся списке в нижней части окна выберите область, в которую нужно поместить поле. В завершение для вставки поля щелкните мышью кнопку Добавить в (Add to) (расположенную под списком).

 

 

3. Теперь нужно выбрать данные для анализа. Переместите поле OrderQty в область Перетащите сюда поля итогов или деталей (Drop Totals or Details Fields Here).

Этот шаг заполняет сводную таблицу данными (программе Access может понадобиться не­сколько мгновений для группировки всех данных, если у вас таблица большого объема).

Поле OrderQty добавлено как подробность, т. е. вы увидите все записи в таблице (или запросе) в соответствующих группах (рис. 9.13).

4. Пришло время добавить итоговые вычисления. Щелкните правой кнопкой мыши в сводной таблице поле OrderQty (подойдет любое поле OrderQty) и выберите вариант из
подменю Автовычисления (AutoCalc).

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

Автовычисления → Сумма, если хотите найти общее количество товаров, проданных в дан­ной категории.

Рис. 9.13. В этой сводной таблице показаны все значения поля OrderQty из запроса Orderedltems, но их немного трудно анализировать. Для того чтобы составить общее представление, необходимо скрыть некоторые из этих деталей и взглянуть на итоговую информацию

 

 

Все сводные данные, которые создаются с помощью подменю Автовычисления, называют итогами. Они добавляются в группу Итоги (Totals) в верхней части окна Список полей сводной таблицы. (Щелкните кнопкой мыши квадратик со знаком +/-, расположенный рядом со словом Итоги для вывода на экран содержимого группы.) Для удаления итога щелкните его в списке правой кнопкой мыши и выберите команду Удалить (Delete).

5. Для скрытия подробной информации и отображения только сводных данных снова щелкните правой кнопкой мыши поле OrderQty и выберите команду Без подробностей (Hide Details).

После того как подробности будут скрыты, вы получите приемлемый результат, напоми­нающий перекрестный запрос (рис. 9.14).

 

 

Подсказка

Если вы знаете наперед, что никогда не захотите отображать подробности, можно добавить итоги сразу же. Для этого выберите поле (в данном случае OrderQty) в окне Список полей сводной таблицы и затем в раскрывающемся списке в нижней части этого окна выберите ва­риант Данные (Data Area). Далее щелкните мышью кнопку Добавить в. Этот подход удобен при работе с громадными таблицами, содержащими тысячи записей. В такой ситуации быстрее добавить итог, а не подробные сведения из каждой записи.

Рис. 9.14. Если убрать с дороги подробности, можно сразу увидеть признанные и не пользующиеся спросом группы. Итог для каждой строки выводится в конце строки (здесь не показан), а итог для каждого столбца отображается в строке Общие итоги в нижней части столбца