Перекрестные запросы в access примеры

Перекрестные запросы в access примеры

На этом шаге будут рассмотрены перекрестные запросы.

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

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

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

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

• Заголовки строк — выбирается для поля запроса, значения которого нужно использовать в качестве заголовков строк. В качестве заголовков строк необходимо определить хотя бы одно поле, при этом в строке Групповые операции необходимо выбрать значение Группировка, одну из итоговых функций или Выражение.
• Заголовки столбцов — выбирается для поля запроса, значения которого нужно использовать в качестве заголовков столбцов. С этой целью используется только одно поле, требования к которому аналогичны требованиям к полю, в котором установлено значение Заголовки Строк.
• Значение — выбирается для поля запроса, в котором вычисляется итоговое значение, отображаемое в «ячейках» перекрестного запроса. Такое поле должно быть единственным, при этом в строке Групповые операции для него необходимо выбрать одну из итоговых функций или задать выражение, в котором используются итоговые функции.
• (не отображается) — выбирается для того поля, значения которого не должны отображаться в перекрестном запросе.

В первом столбце запроса нужно выбрать поле Фамилия таблицы Студенты, при этом следует задать для него значение Группировка в поле Групповая операция, а также Заголовки строк в поле Перекрестная таблица. Также можно выбрать направление сортировки по возрастанию.

Для второго столбца запроса необходимо выбрать поле Название таблицы Предметы и установить для него значение Группировка в поле Групповая операция и Заголовки столбцов в поле Перекрестная таблица.

В третьем столбце запроса нужно выбрать поле Оценка таблицы Успеваемость и задать для него функцию Sum в поле Групповая операция, а также Значение в поле Перекрестная таблица.

Созданный запрос можно сохранить под именем ОценкиПоПредметам (рис. 1).

Рис. 1. Макет перекрестного запроса ОценкиПоПредметам

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

Рис. 2. Результат выполнения запроса ОценкиПоПредметам

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

На следующем шаге вы узнаете о запросах на изменение.

Предыдущий шаг Содержание Следующий шаг

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

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

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

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

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

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

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

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

1. Создайте новый запрос в режиме Конструктора и добавьте в него таблицы «Товары» (Products), «Заказы» (Orders) и «Заказано» (Order Details).
2. Перетащите поля «КодТовара» (ProductlD) и «Марка» (ProductName) таблицы «Товары», а затем поле «ДатаРазмещения» (OrderedDate) таблицы «Заказы» в первые три столбца бланка запроса.
3. Выберите команду меню Запрос, Перекрестный (Query, Crosstab Query). Заголовок окна запроса Запрос1:на выборку (Query1:Select Query) изменится на Запрос!перекрестный запрос (Queryl:Crosstab Query). Кроме того, в бланк запроса будут добавлены строки Перекрестная таблица (Crosstab) и Групповая операция (Total), в которую во всех столбцах автоматически вводится операция Группировка (Group By).
4. Выберите в списке ячейки Перекрестная таблица столбца «КодТовара» значение Заголовки строк (Row Heading). Выполните то же самое для столбца «Марка». Эти столбцы являются требуемыми заголовками строк перекрестной таблицы.
5. Выберите в списке ячейки Групповая операция столбца «ДатаРазмещения» значение Условие (Where). В ячейке Условие отбора (Criteria) этого столбца введите выражение =#01.01.97# для вывода в перекрестной таблице данных за 1997 год.
6. Установите курсор в ячейку Поле (Field) следующего (пустого) столбца и введите выражение:

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

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

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

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

1. Установите курсор в ячейку Поле (Field) следующего (пустого) столбца и введите выражение Format([ДатаРазмещения];»mmm»). Функция Format О в данном случае будет возвращать три первые буквы месяца от значения даты в столбце «ДатаРазмещения». С помощью этой функции мы создали еще одно вычисляемое поле в запросе, причем после окончания ввода этого выражения Access автоматически создает имя этого поля «Выражение!» (Exprl). Так происходит всякий раз, когда мы не определяем имя вычисляемого поля. Выберите из списка в ячейке Перекрестная таблица этого столбца значение Заголовки столбцов (Column Heading) (рис. 8.14). Это означает, что данный столбец запроса будет преобразован в процессе выполнения запроса в строку, содержащую заголовки столбцов результирующей таблицы.

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

1. Нажмите кнопку Запуск (Run). Появится результирующее множество записей, представленное на рис. 8.15.

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

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

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

Как организовать дистанционное обучение во время карантина?

Помогает проект «Инфоурок»

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. Выбрать поля, значения которых использовать в качестве заголовков строк — Сотрудник.

4. Выбрать поля, значения которых использовать в качестве заголовков столбцов – Дата заказа .

5. Выбрать интервал, с которым необходимо сгруппировать столбец данных типа даты и времени — Месяц

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

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

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

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

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

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

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

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

3. Чтобы подключить групповые функции , нажать на кнопку

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

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

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

Поле ФИО сотрудника для заголовков столбцов ,

Поле ФИО клиента для заголовков строк ,

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

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

6. Сохраните запрос под именем Количество заказов по сотрудникам .

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

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

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

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

Задача, для решения которой потребуется перекрестный запрос, формулируется так: необходимо выяснить, сколько аварий (чрезвычайных ситуаций) определенных видов произошло на территории разных субъектов Российской Федерации (краев и областей). Источником информации послужит таблица 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, где показан тот же фрагмент таблицы, что и в предыдущем случае. Во-первых, число аварий по регионам возросло (Иркутская, Кемеровская области). Во-вторых, увеличился список регионов, например добавилась Мурманская область, которой раньше не было в этом перечне.

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

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