На профессиональном уровне. Правильный выбор групп

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

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

Например, если группировать по названию товара и стране, можно биться об заклад, что вы в результате получите больше групп товаров, чем стран. (У вас могут быть клиенты в восьми разных странах, а каталог товаров с 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).

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

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

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

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

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

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

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

Примечание

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