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

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

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

Перекрестный запрос. Access 2002: Самоучитель

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

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

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

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

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

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

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

Рис. 11.64

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

Рис. 11.65

Рис. 11.66

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

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

Рис. 11.67

Рис. 11.68

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

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

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

Рис. 11.69

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

Рис. 11.70

Рис. 11.71

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

Рис. 11.72

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

Рис. 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

Запрос версии сервера Firebird
Переключатель -z (без параметров) показывает версию gfix и сервера Firebird, инсталлированного на сервере. Вот синтаксис:gfix

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

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

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

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

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

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

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

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

Что такое CSRF | Пример подделки межсайтовых запросов

Что такое CSRF

Подделка межсайтовых запросов (CSRF), также известная как XSRF, Sea Surf или Session Riding, представляет собой вектор атаки, который обманом заставляет веб-браузер выполнять нежелательное действие в приложении, которое пользователь вошел в систему.

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

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

Пример CSRF

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

Например, типичный запрос GET для банковского перевода в размере 100 долларов США может выглядеть так:

 GET http://netbank.com/transfer.do?acct=PersonB&amount=$100 HTTP/1.1 

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

 GET http://netbank. com/transfer.do?acct=AttackerA&amount=$100 HTTP/1.1 

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

 Подробнее! 

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

Обратите внимание: если веб-сайт банка использует только POST-запросы, невозможно обработать вредоносные запросы с помощью тега href . Однако атака может быть осуществлена ​​в теге