Перекрестный запрос в access 2020 - IT Новости из мира ПК
Remkomplekty.ru

IT Новости из мира ПК
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

Перекрестные запросы — это запросы, в которых происходит статистическая обработка данных, результаты которой выводятся в виде таблицы, очень похожей на сводную таблицу 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. Результирующее множество перекрестного запроса

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

Работе в программе в Microsoft Access

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

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

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

  • В появившемся окне Crosstab Query Wizard(Создание перекрестного запроса) нужно выбрать таблицу (Töötaja), которая будет служить источником данных для создаваемого запроса.

  • Нажать кнопку Next(Далее).
  • В следующем окне нужно выбрать значения какого поля будут использоваться в заголовках строк. Мы выбираем поле Ameti_kood (код должности) и с помощью стрелки переносим в список Selected Fields.

  • Нажать кнопку Next (Далее).
  • Затем укажите поля, которые станут заголовками столбцов. В данном случае выбираем поле Sugu

  • Нажать кнопку Next(Далее).
  • В следующем окне Мастера предстоит ответить еще на один вопрос: What number do you want calculated for each column and row intersection? (Что вы хотите вычислить для каждой ячейки, расположенной на пересечении строки и столбца?)
  • В нашем случае, мы выбираем поле Staaz (Cтаж) и будем высчитывать средний (Avg) стаж для каждого работника каждой специальности. Т.е .Avg(Staaz)

  • Нажать кнопку Next(Далее).
  • Следующее диалоговое окно будет последним. В нем нужно ввести имя создаваемого запроса в поле What do you want to name your query? (Задайте имя запроса) и выбрать дальнейшие действия:
    View the query(Открыть запрос для просмотра данных) или
    Modify the design(Изменить макет запроса).

  • Нажать кнопку Finish (Готово).
  • Для просмотра откроется запрос, в котором можно увидеть средний стаж работы по каждой специальности, в том числе отдельно для мужчин и женщин.

Практическая работа. Создание перекрестных запросов в 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. Сохраните запрос под именем Количество заказов по сотрудникам .

Перекрестный запрос — понятие, использование и создание

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

Понятие и использование

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

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

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

Выражения, сортировка, условия запроса

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

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

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

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

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

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

На вкладке «Создание» нажимаем «Мастер запросов» и в открывшемся окне выбираем тип «Перекрестный», а затем таблицу для выборки – «Вазы».

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

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

Получаем результат. В нем указано, что магазин может предложить заказчику 25 средних ваз из стекла или фарфора.

Создание в «Конструкторе»

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

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

В «Конструкторе запросов» выбираем все эти таблицы. В строках содержатся данные по складам с номерами и адресами, в столбцах – наименования ваз, в ячейках – количество товаров.

Судя по таблице результатов, мы можем предложить покупателю 3 склада, в которых имеется необходимое количество одинаковых ваз, – № 2, 4 и «Центральный». В первых двух присутствует только одно наименование товара в нужном количестве, в последнем – три.

Использование SQL

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

Данные можно получить обычным запросом на выборку SELECT. В отбираемых полях указываются те столбцы, которые в итоговом запросе составляют заголовки строк. В нашем примере это поля «Склад» и «Адрес» из таблицы «Склады». Для добавления остальных таблиц используется инструкция INNER JOIN с указанием полей для связки.

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

Запрос SQL приобретает вид:

TRANSFORM Sum([Товары по складам].Количество) AS [Sum-Количество]

SELECT Склады.Склад, Склады.Адрес

FROM Склады INNER JOIN (Вазы INNER JOIN [Товары по складам] ON Вазы.Код = [Товары по складам].Наименование) ON Склады.Код = [Товары по складам].Склад

GROUP BY Склады.Склад, Склады.Адрес

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

Перекрестные SQL запросы в СУБД Access

Перекрестные SQL запросы в СУБД Access 2003 и 2007

В данной статье рассмотрим перекрестные SQL запросы на выборку данных из таблиц БД Access. Для создания перекрестного SQL запроса откроем базу данных sql_training_st1_param.mdb.

Перекрестные запросы применяются в том случае если нужно упорядочить информацию из базы данных по двум или более параметрам. Cоздать перекрестный запрос в СУБД Access 2003 — 2007 можно используя, Мастер создания перекрестных запросов, конструктор запросов и режим SQL. Например, в результате выполнения обычного запроса на выборку получим несгруппированные по фамилиям и дисциплинам данные (рис.1).

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

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

Для создания перекрестного SQL запроса или инструкции SQL (рис. 2) необходимо в открытой БД sql_training_st1_param.mdb на вкладке «Создание» выбрать команду «Конструктор запросов». Откроется активное окно диалога «Добавление таблицы» на фоне неактивного окна «Запрос1», далее надо закрыть окно диалога «Добавление таблицы». Затем на контекстной вкладке «Конструктор» выбрать режим SQL, выполнив команду SQL, в результате в окне редактирования будет отображаться оператор SELECT.

Удалим оператор SELECT, и введем с клавиатуры следующую инструкцию SQL:
TRANSFORM Min(Успеваемость.Оценка) AS [Min-Оценка]
SELECT Студенты.Фамилия, Студенты.Имя
FROM Студенты INNER JOIN (Дисциплины INNER JOIN Успеваемость ON Дисциплины.КодДисциплины = Успеваемость.КодДисциплины) ON Студенты.КодСтудента = Успеваемость.КодСтудента
GROUP BY Студенты.Фамилия, Студенты.Имя
PIVOT Дисциплины.Название;

Эта инструкция состоит из пяти предложений «TRANSFORM. «, «SELECT. . .», «FROM. . .», «GROUP BY. » и «PIVOT. «.
Первое предложение содержит оператор TRANSFORM, который является необязательным. Но если его включить, то его надо включить первым оператором в инструкцию SQL. За этим оператором была включена агрегатная функция «Min(Успеваемость.Оценка) AS [Min-Оценка]».

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

Третье предложение содержит оператор FROM и идентификатор «Студенты INNER JOIN (Дисциплины INNER JOIN Успеваемость ON Дисциплины.КодДисциплины = Успеваемость.КодДисциплины) ON Студенты.КодСтудента = Успеваемость.КодСтудента». FROM — определяет таблицы «Студенты», «Дисциплины» и «Успеваемость», которые содержат поля, и обеспечивает взаимосвязи таблиц через ключевые поля таблиц с помощью конструкции INNER JOIN . ON.

Оператор GROUP BY используется для определения групп «Студенты.Фамилия, Студенты.Имя», к которым применяется агрегатная функция Min.

В операторе PIVOT перечислены столбцы для вывода информации о дисциплинах.

В результате выполнения команды «Сохранить» в «Области переходов» появится объект — «Запросы: cros-tab_sql». После сохранения перекрестного SQL запроса на выборку данных необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить» (рис.3).

Результаты выполнения команды «Выполнить» представлены на рис. 4.

Читать еще:  No access token passed
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×