что такое перекрестный запрос в access

Перекрестные запросы

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

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

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

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

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

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

Объем продаж: Sum([Количество]*[Заказано].[Цена])

Это будет поле, значение которого вычисляется с помощью описанного выражения, а название поля — «Объем продаж». В выражении используются поля из таблиц, которые включены в запрос, однако обратите внимание, что сами поля в результат запроса не включены. Это означает, что при создании вычисляемого поля в выражение можно включать ссылки не только на поля самого запроса, но и на поля, которые не включаются в результат запроса. Важно, чтобы они были в исходных таблицах. При ссылке на поле «Цена» в выражении мы указали еще имя таблицы «Заказано», а при ссылке на поле «Количество» не указывали. Указать имя таблицы пришлось потому, что поле с именем «Цена» присутствует и в таблице «Товары» и в таблице «Заказано». Если не указать в выражении имени таблицы, Access не сможет определить, из какой таблицы брать значения, поэтому при выполнении запроса выдаст сообщение об ошибке, как это представлено на рис. 8.13.

Рис. 8.13. Сообщение об ошибке в выражении вычисляемого поля

Выберите в ячейке Групповая операция (Total) того же столбца значение Выражение (Expression), а затем в ячейке Перекрестная таблица — значение Значение (Value). В столбце «Объем продаж» вычисляется общий объем заказов на каждый товар, который будет подставляться в ячейки результирующей таблицы запроса.

Рис. 8.14. Перекрестный запрос в режиме Конструктора

Рис. 8.15. Результирующее множество перекрестного запроса

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

Источник

Практическая работа. Создание перекрестных запросов в MS Access

Ищем педагогов в команду «Инфоурок»

Практическая работа №5

Тема: «Создание перекрестных запросов»

для студентов 2 курса специальности 10.02.03

Информационная безопасность автоматизированных систем

Цели работы: научиться создавать перекрестные запросы на выборку данных с помощью мастера и конструктора запросов.

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

возможностью обработки значительного объема данных и вывода их в формате, который очень хорошо подходит для автоматического создания графиков и диаграмм;

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

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

Создать запрос с помощью мастера форм: Создание/Мастер запросов

Создать с помощью конструктора: Создание/ Конструктор запросов

Изменить запрос с помощью конструктора: Режим/Конструктор

Задание 1. Создать запрос Выручка по месяцам

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

1. Создать запрос с помощью мастера запросов: Создание/Другие/Мастер запросов/Перекрестный запрос

2. Для создания перекрестного запроса использовать данные таблицы Заказы

6. Выбрать, какие исчисления необходимо провести для каждой ячейки на пересечении строк и столбцов. Выбрать поле Сумма и применить функцию C УММА.

Задание 2. Создать перекрестный запрос «Количество заказов по сотрудникам»

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

1. Создать запрос с помощью конструктора: Создание/Другие/Конструктор запросов

2. Для создания запроса использоватьтаблицы и поля:

таблица Заказы (поля ФИО клиента и Сумма )

таблица Сотрудники (поле ФИО сотрудника ).

4. Для поля Сумма выбирать из выпадающего списка функцию Count – считать количество записей.

5. Чтобы создать перекрестный запрос, нажать на кнопку .

6. Провести настройку перекрестной таблицы. Использовать:

5. Для создания запроса нажимаем кнопку Выполнить!

Курс повышения квалификации

Дистанционное обучение как современный формат преподавания

Курс повышения квалификации

Современные педтехнологии в деятельности учителя

Курс профессиональной переподготовки

Математика и информатика: теория и методика преподавания в образовательной организации

Номер материала: ДБ-871296

Международная дистанционная олимпиада Осень 2021

Не нашли то что искали?

Вам будут интересны эти курсы:

Оставьте свой комментарий

Авторизуйтесь, чтобы задавать вопросы.

Безлимитный доступ к занятиям с онлайн-репетиторами

Выгоднее, чем оплачивать каждое занятие отдельно

Путин попросил привлекать родителей к капремонту школ на всех этапах

Время чтения: 1 минута

В Воронежской области ввели масочный режим в школах

Время чтения: 2 минуты

Минобрнауки: вузы вправе вводить QR-коды для посещения корпусов

Время чтения: 2 минуты

Пензенские родители смогут попасть в школы и детсады только по QR-коду

Время чтения: 1 минута

Минпросвещения будет стремиться к унификации школьных учебников в России

Время чтения: 1 минута

СК предложил обучать педагогов выявлять деструктивное поведение учащихся

Время чтения: 1 минута

Подарочные сертификаты

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

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

Источник

Перекрестный запрос

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

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

Построение перекрестного запроса с использованием мастера

Задача, для решения которой потребуется перекрестный запрос, формулируется так: необходимо выяснить, сколько аварий (чрезвычайных ситуаций) определенных видов произошло на территории разных субъектов Российской Федерации (краев и областей). Источником информации послужит таблица Fiie1.

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

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

Когда вы сделаете выбор, на экране появится следующее окно мастера перекрестных запросов (см. рис. 11.65), где вам следует решить, значения каких полей вы хотите оформить в виде заголовков строк. Всего можно указать не более трех полей. Для нашей конкретной задачи в качестве заголовков строк послужат значения поля Регион. Щелкните по кнопке Далее, а затем укажите поля (не больше трех), значения которых станут заголовками столбцов. В данном случае будет выбрано поле Наименование ЧС. После этого вы окажетесь в следующем окне мастера (см. рис. 11.66), где предстоит определить, что же вы хотите вычислить для каждой ячейки, расположенной на пересечении столбца и строки. Вы уже использовали два поля: Название области – для заголовков строк, а Наименование ЧС – для заголовков столбцов.

Теперь понадобится третья переменная, значения которой можно подсчитывать, причем они должны соответствовать значениям первых двух полей. Главное, чтобы это третье указанное поле не имело пропусков, то есть нулевых значений. Следовательно, лучше всего подойдет поле Номер. В качестве функции для подсчета числа значений выберите Число (одна из функций групповой обработки данных). Иначе говоря, в конце концов будет определено количество значений поля Номер для каждой ячейки, которая находится на пересечении столбца (ЧС определенного вида) и строки (того или иного региона Российской Федерации) – рис. 11.66. Теперь, когда вы подготовили все необходимое для создания запроса, щелкните по кнопке Далее.

В результате на экране появится окно (см. рис. 11.67), где в формируемый запрос нужно внести последние уточнения. Во-первых, его надо назвать. В принципе это ваше дело, но мы бы посоветовали принять имя, которое по умолчанию предлагает Access 2002: File1-Перекрестный. Затем надо определить, чего вы хотите: выполнить запрос или изменить его оформление. Выберите соответствующую позицию переключателя. Если вы собираетесь выполнить запрос и дизайн вас не интересует, откажитесь от следующего предложения мастера: вывести инструкцию по работе с запросом. Теперь остается только щелкнуть по кнопке Далее, и запрос будет запущен на выполнение. А вот дальше в нашем размеренном сюжете возникает неожиданная интрига – сообщение В перекрестном запросе слишком много заголовков столбцов – 507. Это означает, что последующее выполнение запроса невозможно (см. рис. 11.68). Озабоченность Access вполне понятна.

Фактически вы объявили заголовками столбцов или полей все наименования ЧС в таблице, кроме их дубликатов, которые система не пропустит (вместе с повторяющимися именами таких заголовков было бы не 507, а значительно больше). Действительно, многовато. Объяснение здесь простое: при формировании перекрестного запроса вы не вводили каких-либо критериев отбора. Впрочем, при использовании мастера перекрестных запросов этого сделать все равно нельзя. Если вы хотите задать такие условия, то нужно сформировать обычный запрос.

Ввод условий отбора записей в конструкторе запросов

В окне базы данных на вкладке Запрос откройте ваш перекрестный запрос в режиме конструктора (см. рис. 11.69). Теперь надо задать критерии отбора записей. Будем считать, что нас по-прежнему интересует количество ЧС по регионам, но только если эти ЧС связаны с пожарами.

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

Предположим, необходимо видоизменить запрос. Нужна справка о числе ЧС по регионам, но теперь нас интересуют аварии, связанные не только с пожарами, но и со взрывами. Кроме условия *пожар* введите еще один критерий отбора – *взрыв*, используя схему «ИЛИ». Иными словами, вы запрашиваете число аварий по регионам, в которых произошли либо пожары, либо взрывы. Если бы вы применили схему «И», система Access 2002 стала бы отбирать сведения из регионов, где произошли и пожары, и… (а это уже совсем другой разговор). Запрос будет выглядеть так, как показано на рис. 11.72.

Результат этого дополненного запроса вы видите на рис. 11.73, где показан тот же фрагмент таблицы, что и в предыдущем случае. Во-первых, число аварий по регионам возросло (Иркутская, Кемеровская области). Во-вторых, увеличился список регионов, например добавилась Мурманская область, которой раньше не было в этом перечне.

Итак, в настоящем разделе мы подробно рассмотрели структуру перекрестного запроса и уяснили для себя существенное правило: чтобы обработать группу записей и получить справку по множеству объектов, надо сначала четко определить само множество. В данном конкретном примере нам необходимо суммарное число ЧС определенного вида по разным российским регионам. Значит, в первую очередь надо получить такие сведения по каждому из этих регионов. В ряде случаев для решения указанной задачи придется создать запрос на выборку.

Данный текст является ознакомительным фрагментом.

Продолжение на ЛитРес

Читайте также

11.3. Запрос и изменение информации inode

11.3. Запрос и изменение информации inode 11.3.1. Поиск информации inode В начале этой главы информационный узел файла (inode) был представлен как структура данных, которая отслеживает информацию о файле, независимо от представления ее для процесса. Например, размер файла является

20.9.2 Запрос get и ответ на него

20.9.2 Запрос get и ответ на него На рис. 20.10 показаны запрос get-request и ответ на него (response), полученные в анализаторе Sniffer компании Network General. Запрос содержит список из пяти переменных, значения которых нужно получить. После каждого идентификатора переменной стоит заполнитель NULL.

20.9.3 Запрос get-next и ответ на него

20.9.3 Запрос get-next и ответ на него Сообщение get-next работает по-другому. Когда отсылается идентификатор объекта, возвращается значение следующего объекта. Например, если послать запрос:SNMP: Object = <1.3.6.1.2.1.5.1.0>(icmpInMsgs.0)SNMP: Value = NULLответ будет содержать имя и значение для следующей

20.9.4 Запрос set

20.9.4 Запрос set Запрос set позволяет записывать информацию в базу данных агента. Формат сообщения очень прост, он выглядит как get-request, но приводит к изменению указанных в запросе переменных. На рис. 20.11 показано отслеживание запроса set.SNMP: Version = 0SNMP: Community = xyzSNMP: Command = Set requestSNMP:

Уточняем запрос в поисковой системе

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

Что такое запрос

Что такое запрос Запрос (query) — это команда базы данных, осуществляющая выборку записей. Используя запросы, можно получить данные из одного или нескольких полей, принадлежащих одной или нескольким таблицам. При этом данные можно отбирать в соответствии с определенными

Запрос версии сервера Firebird

Запрос на удаление записей

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

АВАР/4 Запрос

АВАР/4 Запрос Конечные пользователи могут создавать простые отчеты с помощью АВАР/4 Query. С помощью удобного интерфейса пользователь может указать область или предмет, который его интересует, а также соответствующие таблицы, желаемые поля и оформление списка. Система

Аутентификация «запрос-ответ»

Аутентификация «запрос-ответ» Как показано на рис. 2.2, сервер генерирует случайный запрос и отправляет его пользователю А [208]. Вместо того чтобы в ответ отправить серверу пароль, пользователь А шифрует запрос при помощи ключа, известного только ему самому и серверу.

Неявный запрос на базе времени

Неявный запрос на базе времени Рис. 2.3 иллюстрирует аутентификацию на базе времени [72]. Пользователь А шифрует значение текущего времени на часах своего компьютера и отправляет свое имя и шифртекст на сервер. Сервер расшифровывает значение, присланное пользователем А.

Источник

Улучшение наглядности сводных данных с помощью перекрестного запроса

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

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

В этой статье

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

Общие сведения

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

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

1. Этот запрос на выборку группирует сводные данные вертикально по сотрудникам и категориям.

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

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

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

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

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

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

4. Здесь показаны сводные значения.

Способы создания перекрестных запросов

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

У мастера есть три преимущества:

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

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

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

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

Тем не менее с помощью мастера невозможно сделать следующее:

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

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

добавить запрос на ввод параметров;

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

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

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

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

У вас есть больше контроля над процессом. Мастер принимает некоторые решения за вас.

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

требуется запрашивать ввод параметров;

в качестве полей запроса нужно использовать выражения;

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

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

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

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

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

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

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

На вкладке Создание в группе Запросы нажмите кнопку Мастер запросов.

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

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

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

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

В данном примере выберите ИДы поставщиков.Value и нажмите кнопку с символом >. Обратите внимание на то, что Access отображает имя поля в левой части окна предварительного просмотра запроса внизу диалогового окна. Нажмите кнопку Далее, чтобы продолжить.

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

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

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

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

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

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

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

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

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

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

Если вы использовали таблицу «Товары» из базы данных «Борей», перекрестный запрос отобразит список поставщиков в виде строк, названия категорий продуктов в виде столбцов, а количество товаров на их пересечении.

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

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

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

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

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

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

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

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

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

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

На вкладке Конструктор в группе Тип запроса щелкните элемент Перекрестная таблица.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Инструкция TRANSFORM состоит из следующих элементов:

Агрегатная функция SQL, обрабатывающая выбранные данные.

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

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

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

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

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

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

На вкладке объекта SQL введите или вставьте следующий код:

В первой строке, после инструкции TRANSFORM, введите выражение для расчета сводных значений, например Sum([Кол-во]).

Если в качестве источника записей используется больше одной таблицы или одного запроса, укажите имя таблицы или запроса как часть имени каждого поля, например Sum([Расходы].[Кол-во]).

Во второй строке, после инструкции SELECT, введите список полей или выражений полей, которые требуется использовать в качестве заголовков строк. Элементы списка отделяются друг от друга запятыми, например: [Бюджет].[ ИД_отдела ], [Расходы].[Тип].

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

В четвертой строке, после инструкции GROUP BY, укажите те же поля, что и в предложении SELECT на шаге 6.

В пятой строке, после инструкции PIVOT, введите имя поля или выражение, которое требуется использовать для заголовков столбцов, например PIVOT [Бюджет].[Го д].

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

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

В новой строке введите ORDER BY, а затем — пробел.

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

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

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

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

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

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

Введите WHERE, а затем — условие для поля.

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

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

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

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

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

В бланке запроса в строке Поле щелкните правой кнопкой мыши пустой столбец и выберите в контекстном меню пункт Масштаб.

В поле Масштаб введите псевдоним поля и двоеточие ( :).

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

Предположим, например, что создаются диапазоны для поля «Возраст», причем каждый диапазон должен охватывать двадцать лет. Выражением сравнения для первого диапазона будет [Возраст] [Возраст] «0-20 лет».

После имени диапазона введите запятую (вне кавычек) и выполните одно из указанных ниже действий.

Чтобы создать другой диапазон, введите IIf() и повторите действия 5, 6 и 7.

Для последнего диапазона введите только имя.

Например, полное выражение IIf с вложенными подвыражениями, разбивающее поле «Возраст» на двадцатилетние диапазоны, может выглядеть следующим образом (разрывы строк добавлены для наглядности):

Примечание: Процедура вычисления выражения в Access будет прекращена при обнаружении первой же инструкции IIf, результатом вычисления которой окажется «истина». Нижние границы диапазонов задавать необязательно, поскольку любое значение, меньшее нижней границы данного диапазона, уже дало бы результат «истина».

В бланке запроса в строке Всего выберите вариант Группировка.

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

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

Предложение ввести параметр можно добавить для любого поля заголовков строк.

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

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

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

Например, если ввести вопрос [Какая страна (регион)?] в строке Условие отбора, то при выполнении запроса откроется диалоговое окно, содержащее этот вопрос («Какая страна (регион)?»), поле ввода и кнопку ОК.

Совет: Чтобы сделать параметр более гибким, добавьте к выражению подстановочные знаки с помощью оператора Like. Например, вместо [Какая страна или регион?] в качестве критерия можно использовать like [Какая страна или регион?] &»*», чтобы параметр совпадал с диапазоном входных данных. Использование оператора Like не изменяет вид предложения ввести параметр.

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

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

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

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

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

Синтаксис функции Nz

Функция Nz имеет аргументы, указанные ниже.

Обязательный аргумент. Переменная, имеющая тип данных Variant.

Необязательный аргумент (если он используется не в запросе) типа Variant. Значение Variant, которое обеспечивает возвращаемую величину, если аргумент variant имеет значение Null. Этот аргумент позволяет вернуть значение, отличное от нуля или пустой строки.

Примечание: Если функция Nz используется в выражении запроса без аргумента Значение_если_null , в результате выполнения этой функции для полей, содержащих пустые значения, будет возвращена пустая строка.

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

Выберите в контекстном меню команду Масштаб.

В поле Масштаб заключите имя поля или выражение в скобки и введите перед скобками Nz.

Например, при использовании функции Nz в поле «Потерянные часы» для замены пустых значений нулями законченное выражение будет выглядеть следующим образом:

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

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

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

Используйте вложенный запрос в предложении WHERE. В перекрестном запросе можно использовать вложенный запрос как часть предложения WHERE.

Источник