3. Перекрестные запросы, запросы на создание таблицы
3.1 Перекрестный запрос
Перекрестный запрос — это разновидность запрос на выборку. Результаты выполнения перекрестного запроса выводятся в виде таблицы, структура которой отличается от обычных таблиц.
Благодаря своеобразной структуре перекрестного запроса его легче читать, чем простой запрос на выборку, возвращающий те же данные.
Рис. 1
1. Этот запрос на выборку группирует сводные данные вертикально по сотрудникам и категориям.
2. Перекрестный запрос показывает те же данные, но группирует их и по горизонтали, и по вертикали, в результате чего таблица становится более компактной и наглядной.
Создание перекрестных запросов. При создании перекрестного запроса необходимо указать, в каких полях должны содержаться заголовки строк, в каких — заголовки столбцов, а в каких — значения, по которым вычисляются сводные данные. При задании заголовков столбцов и суммируемых значений можно использовать только одно поле. При задании заголовков строк можно использовать до трех полей. Для определения суммируемых значений и заголовков строк и столбцов можно также использовать выражения.
Рис. 2
1. Эта часть содержит один, два или три столбца с заголовками строк. Имена полей, используемых в качестве заголовков строк, показаны в верхней строке этих столбцов.
2. Здесь показаны заголовки строк. Число строк в перекрестной таблице может быстро возрасти, если использовать более одного поля заголовков строк, поскольку отображаются все комбинации заголовков строк.
3. В столбцах с этой стороны содержатся заголовки столбцов и сводные значения. Обратите внимание: имя поля заголовков столбцов не отображается в таблице.
4. Здесь показаны сводные значения.
Методы создания перекрестного запроса
1.Использование мастера создания перекрестных запросов. Быстрее и легче всего создавать перекрестные запросы с помощью мастера. Он выполняет большую часть работы, однако существует несколько параметров, которые он не предлагает определять.
Преимущества использования мастера:
Простота применения. Достаточно запустить мастер и затем ответить на ряд вопросов.
Автоматическая группировка дат по интервалам. Если для заголовков столбцов используется поле с данными типа дата/время, мастер позволяет сгруппировать даты по интервалам, например по месяцам или кварталам. Если необходимо использовать в заголовках столбцов поле типа «Дата/время», но группировать даты по интервалам, не поддерживаемым мастером, например по финансовым годам или двухлетним периодам, не пользуйтесь мастером для создания запроса. Создайте перекрестный запрос в режиме конструктора и задайте интервалы с помощью выражения.
Мастер может служить отправной точкой. Можно создать с помощью мастера упрощенный перекрестный запрос, а затем уточнить его структуру в режиме конструктора.
Недостатки использования мастера:
использовать более одной таблицы или запроса в качестве источник записей;
использовать выражение для создания полей;
добавлять приглашение на ввод запрос с параметрами;
задавать список фиксированных значений для использования в качестве заголовков столбцов.
На последнем шаге работы мастера предлагается возможность изменить запрос в режиме конструктора. Это позволяет добавить в структуру запроса элементы, не поддерживаемые мастером, например дополнительные источники записей.
2. Работа в режиме конструктора. Режим конструктора позволяет более детально контролировать структуру запроса. В этом режиме доступны возможности, не поддерживаемые мастером.
Создавать перекрестный запрос в режиме конструктора рекомендуется, когда перед пользователем стоят следующие задачи:
Лучше контролировать весь процесс. Мастер принимает некоторые решения независимо от пользователя.
Использовать в качестве источника записей более одной таблицы или запроса.
Включить в запрос приглашение для ввода параметра.
Использовать выражения в качестве полей запроса.
Задать список фиксированных значений для использования в качестве заголовков столбцов.
3. Составление запроса в режиме SQL. При необходимости перекрестный запрос можно составить в режиме SQL. Однако в этом режиме нельзя задавать типы данных параметров. Если в перекрестном запросе должен использоваться параметр, для указания его типа данных следует изменить запрос в режиме конструктора.
При создании перекрестного запроса необязательно ограничиваться каким-то одним методом. Можно создать запрос с помощью мастера, а затем изменить его структуру в режиме конструктора.
Создание перекрестного запроса с помощью мастера. При использовании мастера создания перекрестных запросов необходимо в качестве источника записей для перекрестного запроса выбрать только одну таблицу или один запрос. Если данные, которые требуется включить в перекрестный запрос, содержатся в нескольких таблицах, сначала создайте запрос на выборку, возвращающий требуемые данные.
На вкладке Создание в группе Другие щелкните Мастер запросов.
В диалоговом окне Новый запрос выберите в списке пункт Перекрестный запрос и нажмите кнопкуОК.
Будет запущен мастер создания перекрестных запросов.
На первой странице мастера выберите таблицу или запрос, которые следует использовать для создания перекрестного запроса.
На следующей странице выберите поле, содержащее значения, которые требуется использовать в качестве заголовков строк.
В качестве источников заголовков строк можно выбрать до трех полей, однако чем меньше используется заголовков строк, тем легче будет изучать перекрестную таблицу.
Если для формирования заголовков строк выбирается несколько полей, то порядок их выбора определяет, в каком порядке будут по умолчанию сортироваться результаты.
На следующей странице выберите поле, содержащее значения, которые требуется использовать в качестве заголовков столбцов.
Обычно выбирается поле, содержащее меньше значений, чтобы результаты было удобно читать. Использовать поле с минимальным набором возможных значений (например, пол), как правило, целесообразнее, чем поле, которое может содержать множество различных значений (например, возраст).
Если поле, выбранное для создания заголовков столбцов, содержит данные типа «Дата/время», мастер выполнит дополнительный шаг, предложив указать способ группировки дат по интервалам, например по месяцам или кварталам.
Если для заголовков столбцов выбрано поле типа «Дата/время», на следующей странице мастера будет предложено задать интервал для группировки дат. Возможные варианты: Год, Квартал, Месяц, Дата иДата/время. Если поле, выбранное для формирования заголовков столбцов, не относится к типу «Дата/время», эта страница мастера пропускается.
На следующей странице выберите поле и функцию для расчета сводных значений. Набор доступных функций определяется тип данных поля выбранного поля.
На той же странице установите или снимите флажок Да, чтобы соответственно включить в запрос или исключить из него итоговые значения по строкам.
Если итоги строк включены, в перекрестный запрос добавляется заголовок строки, для которого будут использоваться те же поле и функция, что и для данного значения поля. В результате включения итога по строке вставляется дополнительный столбец, содержащий сводные данные по остальным столбцам. Например, если перекрестный запрос вычисляет средний возраст по расположению и полу (с использованием заголовков столбцов, определяемых полом), в дополнительном столбце будет рассчитываться средний возраст по расположению независимо от пола. Функцию, используемую для получения итогов по строкам, можно изменить в режиме конструктора.
На следующей странице мастера введите имя для запроса и укажите, требуется ли просмотреть результаты или изменить структуру запроса.
Создание перекрестного запроса в режиме конструктора. Создавая перекрестный запрос в режиме конструктора, можно использовать произвольное число источников записей (таблиц и запросов). Однако для упрощения структуры рекомендуется сначала создать запрос на выборку, возвращающий все требуемые данные, а затем использовать этот запрос в качестве единственного источника записей для перекрестного запроса. Для получения дополнительных сведений о создании запроса на выборку воспользуйтесь ссылками из раздела См. также.
При построении перекрестного запроса в режиме конструктора используются строки бланка запроса Групповая операция и Перекрестная таблица, в которых указывается, какие значения поля станут заголовками столбцов, какие — заголовками строк, а какие будут использоваться для вычисления суммы, среднего, количества или других расчетов.
Рис. 3
1. Значения в этих строках определяют, служит ли поле заголовком строки, заголовком столбца или сводным значением.
2. Этот параметр задает отображение значений поля как заголовков строк.
3. Этот параметр задает отображение значений поля как заголовков столбцов.
4. Эти параметры задают получение сводных значений.
Создание запроса
На вкладке Создание в группе Другие щелкните Конструктор запросов.
В диалоговом окне Добавление таблицы дважды щелкните каждую таблицу или запрос, которые следует использовать в качестве источников записей.
Если выбирается несколько источников записей, убедитесь, что эти таблицы и запросы объединение по имеющимся в них общим полям. Для получения дополнительных сведений о соединении таблиц и запросов воспользуйтесь ссылками из раздела См. также.
Закройте диалоговое окно Добавление таблицы.
На вкладке Конструктор в группе Тип запроса выберите команду Перекрестный.
В окне конструктора запроса дважды щелкните каждое поле, которое следует использовать как источник заголовков строк. Можно выбрать до трех полей для создания заголовков строк.
В бланке запроса в строке Перекрестная таблица для каждого поля заголовков строк выберитеЗаголовки строк.
Можно ввести условие в строке Условие отбора, чтобы ограничить число результатов для этого поля. Можно также использовать строку Сортировка, чтобы указать порядок сортировки для поля.
В окне конструктора запросов дважды щелкните поле, которое необходимо использовать в качестве источника заголовков столбцов. Для этой цели можно выбрать только одно поле.
В бланке запроса в строке Перекрестная таблица для каждого поля заголовков столбцов выберитеЗаголовки столбцов.
Можно ввести условие в строке Условие отбора, чтобы ограничить число результатов для поля заголовков столбцов. Однако использование условного выражения для поля заголовков столбцов не ограничивает число столбцов, возвращаемых перекрестным запросом. Ограничения касаются того, какие столбцы могут содержать данные. Например, предположим, что имеется поле заголовков столбцов с тремя возможными значениями: красный, зеленый и синий. Если к полю заголовков столбцов применить условие ='синий', в перекрестной таблице останутся столбцы «Красный» и «Зеленый», но только столбец «Синий» будет содержать данные.
Чтобы ограничить набор значений, отображаемых в качестве заголовков столбцов, можно задать список фиксированных значений, используя свойство запроса Заголовки столбцов.
В окне конструктора запроса дважды щелкните поле, которое следует использовать для расчета сводных значений. Для размещения сводных значений можно выбрать только одно поле.
В бланке запроса в строке Групповая операция для поля сводных значений выберите статистическую функцию, с помощью которой следует вычислять значения.
В строке Перекрестная таблица для поля сводных значений выберите Значение.
Условия отбора или сортировки для поля сводных значений задавать нельзя.
На вкладке Конструктор в группе Результаты выберите команду Запуск.
Задание фиксированных значений для заголовков столбцов
Чтобы задать фиксированные значения для использования в заголовках столбцов, можно воспользоваться свойством запроса Заголовки столбцов.
Откройте перекрестный запрос в режиме конструктора.
Если окно свойств не открыто, откройте его нажатием клавиши F4.
Убедитесь, что в окне свойств над вкладкой Общие указано Возможен выбор: Свойства запроса. В противном случае щелкните в пустом месте над бланком запроса.
В окне свойств на вкладке Общие в строке свойства Заголовки столбцов введите через запятую значения, которые следует использовать в качестве заголовков столбцов.
В заголовках столбцов запрещается использовать некоторые символы (например, большинство знаков препинания). Если такие символы будут вводиться в списке значений, каждый из них будет автоматически заменяться символом подчеркивания (_).
Создание перекрестного запроса в режиме SQL. Синтаксис SQL для перекрестного запроса
Перекрестный запрос определяется в SQL с помощью инструкции TRANSFORM. Инструкция TRANSFORM имеет следующий синтаксис:
TRANSFORM статистическая_функция инструкция_SELECT PIVOT поле_сводной_таблицы [IN (значение1[, значение2[, ...]])]
В режиме SQL число таблиц или запросов, которые можно использовать в качестве источников записей для перекрестного запроса, не ограничено.
Задание порядка сортировки для поля заголовков строк
Чтобы задать порядок сортировки в перекрестном запросе в режиме SQL, используйте предложение ORDER BY.
Вставьте строку между предложениями GROUP BY и PIVOT.
В новой строке введите ORDER BY и пробел.
Введите имя поля или выражение, по которому следует производить сортировку, например: ORDER BY [Расходы].[Класс_расходов]
По умолчанию предложение ORDER BY задает сортировку значений в порядке возрастания. Чтобы задать сортировку по убыванию, введите DESC после имени поля или выражения.
Чтобы задать сортировку еще по одному полю или выражению, введите запятую и после нее дополнительное имя поля или выражение. Сортировка будет выполняться в порядке следования полей и выражений в предложении ORDER BY.
Ограничение числа значений, используемых в заголовках строк и столбцов
Описываемые далее процедуры можно использовать для задания списка значений, которые будут служить заголовками столбцов, и добавления условий отбора в поля заголовков строк. При выполнении этих процедур предполагается, что перекрестный запрос открыт в режиме SQL.
Задание фиксированных значений для заголовков столбцов. В конце предложения PIVOT введите IN и далее в скобках список значений через запятую, которые следует использовать в качестве заголовков столбцов. Например, запись IN (2007, 2008, 2009, 2010)означает создание четырех заголовков столбцов: 2007, 2008, 2009, 2010.
Если какое-либо из указанных фиксированных значений не соответствует значению поля сводной таблицы, это фиксированное значение становится заголовком пустого столбца.
Добавление условий отбора для ограничения числа заголовков строк
Вставьте новую строку после предложения FROM.
Введите WHERE и далее условие для поля.
Если требуется использовать несколько условий, можно расширить предложение WHERE, используя операторы AND и OR. Можно также группировать условия в логически связанные наборы с помощью скобок.
Использование диапазонов и интервалов в качестве заголовков
Иногда вместо того чтобы делать заголовком каждое значение поля, хочется сгруппировать эти значения в диапазоны, чтобы использовать эти диапазоны в качестве заголовков строк или столбцов. Например, предположим, что заголовками столбцов служат значения поля «Возраст». Можно не создавать столбец для каждого возраста, а использовать столбцы, представляющие диапазоны возрастов.
Для создания диапазонов, выступающих в качестве заголовков строк или столбцов, можно использовать функцию IIf. Если требуется создать интервалы для значений типа «Дата/время», рекомендуется воспользоваться мастером создания перекрестных запросов. Он позволяет объединить даты в интервалы Год, Квартал, Месяц,Дата или Дата/время. Если ни один из этих интервалов не подходит, создайте перекрестный запрос в режиме конструктора, а затем с помощью метода, описанного в данном разделе, создайте нужные интервалы.
В перекрестный запрос можно включить приглашение на ввод данных, которое будет выдаваться при выполнении запроса. Например, предположим, что используется несколько заголовков строк, в том числе «Страна/регион». Чтобы не выводить данные по всем странам и регионам, можно определить приглашение на ввод названия, и тогда данные будут отображаться в зависимости от значения, введенного пользователем.
Приглашение на ввод параметра можно добавить для любого поля заголовков строк. Можно добавить такое приглашение и для поля заголовков столбцов, но это не приведет к ограничению числа отображаемых столбцов. Подробнее о том, как ограничить число показываемых столбцов, см. в разделе Задание фиксированных значений для заголовков столбцов.
Откройте перекрестный запрос в режиме конструктора.
В строке Условие отбора для поля заголовков строк, для которого требуется запрашивать ввод данных пользователем, введите текст вопроса в квадратных скобках. Этот текст будет отображаться в качестве приглашения при выполнении запроса.
Например, если ввести вопрос [Какая страна (регион)?] в строке Условие отбора, то при выполнении запроса откроется диалоговое окно, содержащее этот вопрос («Какая страна (регион)?»), поле ввода и кнопку ОК. Чтобы сделать параметр более гибким, добавьте к выражению подстановочные знаки с помощью оператора Like. Например, вместо условия [Какая страна (регион)?] можно задать условиеLike [Какая страна (регион)?]&"*", чтобы расширить диапазон вводимых значений, подходящих для данного параметра. Использование оператора Like не изменит внешний вид приглашения для ввода параметра.
На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Параметры.
В диалоговом окне Параметры запроса в столбце Параметр введите то же приглашение, которое было задано в строке Условие отбора. Поставьте квадратные скобки, но не добавляйте подстановочные знаки и оператор Like.
В столбце Тип данных выберите тип данных для параметра. Он должен соответствовать типу данных поля заголовков строк.
Советы по использованию перекрестных запросов
Не усложнять. С ростом числа комбинаций строк перекрестные запросы становится все труднее читать. Не используйте больше заголовков строк, чем необходимо.
Возможности поэтапного создания перекрестной таблицы. Не ограничивайтесь одними таблицами. Часто бывает удобнее начать с построения итоговый запрос, а затем использовать его как источник записей для перекрестного запроса.
Внимательность при выборе поля заголовков столбцов. Перекрестные таблицы удобнее изучать, когда число столбцов сравнительно невелико. Определив, какие поля следует использовать в качестве заголовков, выберите поле с минимальным числом различающихся значений, которые станут заголовками столбцов. Например, если в запросе вычисляется значение в зависимости от возраста и пола, рекомендуется в качестве заголовков столбцов использовать значения пола, а не возраста, поскольку значений последнего обычно оказывается гораздо больше