Перекрестный запрос - понятие, использование и создание
При работе с базами данных пользователю необходимо получать информацию в удобном для отображения виде с подведением итогов по различным параметрам, с использованием вычислений и выражений. В БД 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 Склады.Склад, Склады.Адрес
PIVOT Вазы.Ваза;
Как мы убедились, перекрестные запросы в базе Access – удобная функция, использовать которую несложно. Она помогает упростить представление данных и облегчить работу над сводными отчетами.