Методы создания перекрестного запроса

1.Использование мастера создания перекрестных запросов. Быстрее и легче всего создавать перекрестные запросы с помощью мастера. Он выполняет большую часть работы, однако существует несколько параметров, которые он не предлагает определять.

Преимущества использования мастера:

· Простота применения. Достаточно запустить мастер и затем ответить на ряд вопросов.

· Автоматическая группировка дат по интервалам. Если для заголовков столбцов используется поле с данными типа дата/время, мастер позволяет сгруппировать даты по интервалам, например по месяцам или кварталам. Если необходимо использовать в заголовках столбцов поле типа «Дата/время», но группировать даты по интервалам, не поддерживаемым мастером, например по финансовым годам или двухлетним периодам, не пользуйтесь мастером для создания запроса. Создайте перекрестный запрос в режиме конструктора и задайте интервалы с помощью выражения.

· Мастер может служить отправной точкой. Можно создать с помощью мастера упрощенный перекрестный запрос, а затем уточнить его структуру в режиме конструктора.

Недостатки использования мастера:

· использовать более одной таблицы или запроса в качестве источник записей;

· использовать выражение для создания полей;

· добавлять приглашение на ввод запрос с параметрами;

· задавать список фиксированных значений для использования в качестве заголовков столбцов.

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

2. Работа в режиме конструктора. Режим конструктора позволяет более детально контролировать структуру запроса. В этом режиме доступны возможности, не поддерживаемые мастером.

Создавать перекрестный запрос в режиме конструктора рекомендуется, когда перед пользователем стоят следующие задачи:

1. Лучше контролировать весь процесс. Мастер принимает некоторые решения независимо от пользователя.

2. Использовать в качестве источника записей более одной таблицы или запроса.

3. Включить в запрос приглашение для ввода параметра.

4. Использовать выражения в качестве полей запроса.

5. Задать список фиксированных значений для использования в качестве заголовков столбцов.

3. Составление запроса в режиме SQL. При необходимости перекрестный запрос можно составить в режиме SQL. Однако в этом режиме нельзя задавать типы данных параметров. Если в перекрестном запросе должен использоваться параметр, для указания его типа данных следует изменить запрос в режиме конструктора.

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

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

1. На вкладке Создание в группе Другие щелкните Мастер запросов.

2. В диалоговом окне Новый запрос выберите в списке пункт Перекрестный запрос и нажмите кнопку ОК.

Будет запущен мастер создания перекрестных запросов.

3. На первой странице мастера выберите таблицу или запрос, которые следует использовать для создания перекрестного запроса.

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

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

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

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

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

Если поле, выбранное для создания заголовков столбцов, содержит данные типа «Дата/время», мастер выполнит дополнительный шаг, предложив указать способ группировки дат по интервалам, например по месяцам или кварталам.

6. Если для заголовков столбцов выбрано поле типа «Дата/время», на следующей странице мастера будет предложено задать интервал для группировки дат. Возможные варианты: Год, Квартал, Месяц, Дата и Дата/время. Если поле, выбранное для формирования заголовков столбцов, не относится к типу «Дата/время», эта страница мастера пропускается.

7. На следующей странице выберите поле и функцию для расчета сводных значений. Набор доступных функций определяется тип данных поля выбранного поля.

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

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

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

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

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

Рис. 3

1. Значения в этих строках определяют, служит ли поле заголовком строки, заголовком столбца или сводным значением.

2. Этот параметр задает отображение значений поля как заголовков строк.

3. Этот параметр задает отображение значений поля как заголовков столбцов.

4. Эти параметры задают получение сводных значений.

Создание запроса

1. На вкладке Создание в группе Другие щелкните Конструктор запросов.

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

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

3. Закройте диалоговое окно Добавление таблицы.

4. На вкладке Конструктор в группе Тип запроса выберите команду Перекрестный.

5. В окне конструктора запроса дважды щелкните каждое поле, которое следует использовать как источник заголовков строк. Можно выбрать до трех полей для создания заголовков строк.

6. В бланке запроса в строке Перекрестная таблица для каждого поля заголовков строк выберите Заголовки строк.

Можно ввести условие в строке Условие отбора, чтобы ограничить число результатов для этого поля. Можно также использовать строку Сортировка, чтобы указать порядок сортировки для поля.

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

8. В бланке запроса в строке Перекрестная таблица для каждого поля заголовков столбцов выберите Заголовки столбцов.

Можно ввести условие в строке Условие отбора, чтобы ограничить число результатов для поля заголовков столбцов. Однако использование условного выражения для поля заголовков столбцов не ограничивает число столбцов, возвращаемых перекрестным запросом. Ограничения касаются того, какие столбцы могут содержать данные. Например, предположим, что имеется поле заголовков столбцов с тремя возможными значениями: красный, зеленый и синий. Если к полю заголовков столбцов применить условие ='синий', в перекрестной таблице останутся столбцы «Красный» и «Зеленый», но только столбец «Синий» будет содержать данные.

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

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

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

11. В строке Перекрестная таблица для поля сводных значений выберите Значение.

Условия отбора или сортировки для поля сводных значений задавать нельзя.

12. На вкладке Конструктор в группе Результаты выберите команду Запуск.

Задание фиксированных значений для заголовков столбцов

Чтобы задать фиксированные значения для использования в заголовках столбцов, можно воспользоваться свойством запроса Заголовки столбцов.

1. Откройте перекрестный запрос в режиме конструктора.

2. Если окно свойств не открыто, откройте его нажатием клавиши F4.

3. Убедитесь, что в окне свойств над вкладкой Общие указано Возможен выбор: Свойства запроса. В противном случае щелкните в пустом месте над бланком запроса.

4. В окне свойств на вкладке Общие в строке свойства Заголовки столбцов введите через запятую значения, которые следует использовать в качестве заголовков столбцов.

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

Создание перекрестного запроса в режиме SQL. Синтаксис SQL для перекрестного запроса

Перекрестный запрос определяется в SQL с помощью инструкции TRANSFORM. Инструкция TRANSFORM имеет следующий синтаксис:

TRANSFORM статистическая_функция
инструкция_SELECT
PIVOT поле_сводной_таблицы [IN (значение1[, значение2[,...]])]

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

Задание порядка сортировки для поля заголовков строк

Чтобы задать порядок сортировки в перекрестном запросе в режиме SQL, используйте предложение ORDER BY.

1. Вставьте строку между предложениями GROUP BY и PIVOT.

2. В новой строке введите ORDER BY и пробел.

3. Введите имя поля или выражение, по которому следует производить сортировку, например: ORDER BY [Расходы].[Класс_расходов]

По умолчанию предложение ORDER BY задает сортировку значений в порядке возрастания. Чтобы задать сортировку по убыванию, введите DESC после имени поля или выражения.

4. Чтобы задать сортировку еще по одному полю или выражению, введите запятую и после нее дополнительное имя поля или выражение. Сортировка будет выполняться в порядке следования полей и выражений в предложении ORDER BY.

Ограничение числа значений, используемых в заголовках строк и столбцов

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

Задание фиксированных значений для заголовков столбцов. В конце предложения PIVOT введите IN и далее в скобках список значений через запятую, которые следует использовать в качестве заголовков столбцов. Например, запись IN (2007, 2008, 2009, 2010)означает создание четырех заголовков столбцов: 2007, 2008, 2009, 2010.

Если какое-либо из указанных фиксированных значений не соответствует значению поля сводной таблицы, это фиксированное значение становится заголовком пустого столбца.

Добавление условий отбора для ограничения числа заголовков строк

1. Вставьте новую строку после предложения FROM.

2. Введите WHERE и далее условие для поля.

Если требуется использовать несколько условий, можно расширить предложение WHERE, используя операторы AND и OR. Можно также группировать условия в логически связанные наборы с помощью скобок.

Использование диапазонов и интервалов в качестве заголовков

Иногда вместо того чтобы делать заголовком каждое значение поля, хочется сгруппировать эти значения в диапазоны, чтобы использовать эти диапазоны в качестве заголовков строк или столбцов. Например, предположим, что заголовками столбцов служат значения поля «Возраст». Можно не создавать столбец для каждого возраста, а использовать столбцы, представляющие диапазоны возрастов.

Для создания диапазонов, выступающих в качестве заголовков строк или столбцов, можно использовать функцию IIf. Если требуется создать интервалы для значений типа «Дата/время», рекомендуется воспользоваться мастером создания перекрестных запросов. Он позволяет объединить даты в интервалы Год, Квартал, Месяц,Дата или Дата/время. Если ни один из этих интервалов не подходит, создайте перекрестный запрос в режиме конструктора, а затем с помощью метода, описанного в данном разделе, создайте нужные интервалы.

В перекрестный запрос можно включить приглашение на ввод данных, которое будет выдаваться при выполнении запроса. Например, предположим, что используется несколько заголовков строк, в том числе «Страна/регион». Чтобы не выводить данные по всем странам и регионам, можно определить приглашение на ввод названия, и тогда данные будут отображаться в зависимости от значения, введенного пользователем.

Приглашение на ввод параметра можно добавить для любого поля заголовков строк. Можно добавить такое приглашение и для поля заголовков столбцов, но это не приведет к ограничению числа отображаемых столбцов. Подробнее о том, как ограничить число показываемых столбцов, см. в разделе Задание фиксированных значений для заголовков столбцов.

1. Откройте перекрестный запрос в режиме конструктора.

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

Например, если ввести вопрос [Какая страна (регион)?] в строке Условие отбора, то при выполнении запроса откроется диалоговое окно, содержащее этот вопрос («Какая страна (регион)?»), поле ввода и кнопку ОК. Чтобы сделать параметр более гибким, добавьте к выражению подстановочные знаки с помощью оператора Like. Например, вместо условия [Какая страна (регион)?] можно задать условиеLike [Какая страна (регион)?]&"*", чтобы расширить диапазон вводимых значений, подходящих для данного параметра. Использование оператора Like не изменит внешний вид приглашения для ввода параметра.

3. На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Параметры.

4. В диалоговом окне Параметры запроса в столбце Параметр введите то же приглашение, которое было задано в строке Условие отбора. Поставьте квадратные скобки, но не добавляйте подстановочные знаки и оператор Like.

5. В столбце Тип данных выберите тип данных для параметра. Он должен соответствовать типу данных поля заголовков строк.

Советы по использованию перекрестных запросов

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

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

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

3.2 Запрос на создание таблицы

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

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

Процесс создания запроса на создание таблицы состоит из следующих основных этапов.

· Включите содержимое базы данных, если она не подписана или не находится в надежном расположении. Иначе нельзя будет запускать запросы на изменение (запросы на добавление, на обновление или на создание таблицы).

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

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

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

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

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

1. На панели сообщений нажмите кнопку Параметры.

2. В диалоговом окне Параметры безопасности Microsoft Office выберите параметр Включить это содержимое и нажмите кнопку ОК.

Если панель сообщений не отображается

· На вкладке Работа с базами данных в группе Отображение выберите параметр Панель сообщений.

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

1. На вкладке Создание в группе Другие щелкните Конструктор запросов.

Рис. 4

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

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

Рис. 5

4. При необходимости добавьте выражения в строку Поле.

5. Можно также добавить любые условия отбора в строку Условия отбора бланка запроса.

6. Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить.

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

Дата добавления: 2015-10-21