Перекрестные запросы используются в тех случаях, когда необходимо разгруппировать имеющиеся данные по определенным критериям отбора. В перекрестных запросах, кроме обычных операций группировки выбранных данных, производится такое их расположение в таблице запроса, которое позволяет более компактно и наглядно отображать выбранную из БД информацию. Таблица перекрестного запроса выглядит примерно так же, как стандартная электронная таблица, где обычно все строки и столбцы имеют свои названия или номер, а на их пересечении размещается соответствующее выбранной ячейке значение.
Поэтому в QBE-бланке перекрестного запроса всегда имеются 3 поля: одно из них используется для названия строк, второе – для названия столбцов и третье – для выбора соответствующего значения, которое должно размещаться на их пересечении.
Рис. 23. Окно создания перекрестного запроса
Рис. 24. Результат выполнения перекрестного запроса
Например, пусть в таблице «Контракты» необходимо разгруппировать заключенные с клиентами контракты по типу товаров. Для организации такого запроса необходимо выбрать в QBE-область поля: «Клиент», «Товар», «Количество» и выполнить команду Запрос/Перекрестный, которая добавит в QBE-бланк строки Групповая операция и Перекрестная таблица (см. рис. 23). Затем в строке QBE-бланка Групповая операция для полей «Клиент» и «Товар» задать функцию Группировка, а для поля «Количество» – функцию Sum. В строке Перекрестная таблица задать для поля «Клиент» функцию Заголовки строк, для поля «Товар» – функцию Заголовки столбцов и для поля «Количество» – функцию Значение. В результате выполнения данного запроса должна появиться таблица вида, представленного на рис. 24.
Выполните созданный запрос и сохраните его под именем «Клиент-товар-количество».
4.7. Запрос в Режиме SQL
В Access запросы представляются в трех режимах: Конструктор, Режим таблицы и Режим SQL. Ранее уже упоминалось, что любой запрос является программой, написанной на языке структурированных запросов SQL. Этот язык является общепринятым стандартным языком запросов в реляционных БД. Он был разработан фирмой IBM для реляционной модели данных, предложенной Э.Ф. Коддом. Фактически программа на SQL представляет собой некоторую фразу-запрос к выборке данных на английском языке, записанную в определенной структуре, которую затем СУБД преобразует в требуемый результат. В данном пособии SQL не будет рассматриваться подробно, однако, вообще говоря, знание основных особенностей этого языка поможет лучше понять процесс выполнения запроса, а также при необходимости отредактировать его или даже построить более эффективный запрос.
Рис. 25. Режим SQL для запроса на просмотр с сортировкой
Для того чтобы перейти в Режим SQL запроса, необходимо выполнить цепочку команд Вид/Режим SQL. После чего на экране открывается окно программы запроса. Для анализа работы операторов языка SQL рассмотрим тексты программ запросов, созданных ранее с помощью Мастера Простых запросов либо Конструктора.
Например, после перехода к режиму SQL ранее сформированный мастером Простых запросов запрос «Клиенты» будет выглядеть так, как это показано на рис. 25. Как видно, текст SQL программы запроса достаточно прост и вполне доступен для понимания с первого раза. В первой строке после оператора SELECT (выбрать, англ.) указываются поля таблицы «Клиенты», которые выбраны для запроса. Во второй строке после оператора FROM (из, англ.) указывается таблица, из которой эти поля выбираются, а в третьей строке после оператора ORDER BY (упорядочить по, англ.) указано поле, по которому проводится сортировка результирующих записей.
Запрос с заданным критерием отбора записей «Контракты после 1 марта», в котором производится выборка по условию, имеет программу с аналогичной структурой. Однако в программу запроса (см. рис. 26) добавлена строка с условием, которое сформировано после оператора WHERE (где, англ.) и по которому из таблицы «Контракты» выбираются все записи с контрактами, заключенными после 1 марта 2001 года.
При задании запроса с группировкой записей и вычислением итоговых значений используется оператор группировки GROUP BY (группировать по, англ.) и соответствующая вычислительная операция Sum по числовым полям для сгруппированных данных (см. рис. 27).
Рис. 26. Режим SQL для запроса с условием на выборку записей
Рис. 27. Режим SQL для запроса с группировкой и вычислением
Если запрос строится на основе полей из нескольких связанных между собой таблиц, то это, естественно, усложняет программу, в которой нужно обеспечить определенное согласование отбираемых для запроса данных. В частности, в более сложном запросе «Товары Запрос», основанном на трех взаимосвязанных таблицах «Товары», «Контракты» и «Сотрудники», появляются операторы INNER JOIN (внутреннее объединение, англ.) для внутреннего объединения таблиц по связующему их полю и DISTINCTROW (отличающаяся запись, англ.) для ограничения выбираемых данных однозначными записями (см. рис. 28).
Перекрестный запрос всегда преобразует таблицу запроса к форме электронных таблиц Excel, поэтому первым оператором в таком запросе является оператор TRANSFORM (преобразовать, англ.), который определяет вычисляемые значения в ячейках результирующей таблицы запроса. После операторов выбора SELECT … FROM и оператора группировки GROUP BY следует оператор PIVOT (опора, англ.), где указывается поле, которое должно использоваться при создании заголовков
столбцов для набора записей перекрестного запроса.
Рис. 28. Запрос в Режиме SQL на основе связанных таблиц
Рис. 29. Перекрестный запрос в Режиме SQL
Перечислим в заключение назначение некоторых основных операторов языка SQL:
SELECT – выбирает поля из таблиц по запросу;
FROM – указывает таблицу, из которой были выбраны поля;
WHERE – создает условие на выборку данных в записях;
ORDER BY – сортирует записи в заданном порядке;
GROUP BY – группирует совпадающие записи при выполнении итоговых запросов;
INNER JOIN – объединяет таблицы по связующим полям;
DISTINCTROW – исключает из результирующего набора повторяющиеся записи;
TRANSFORM – вычисляет выражения в перекрестных запросах;
PIVOT – определяет заголовки столбцов в таблице перекрестного запроса.
Просмотрите и проанализируйте в Режиме SQL другие созданные с помощью Конструктора либо Мастера запросы.
Отредактируйте программы запросов на основе проанализированных выше свойств операторов языка SQL (предварительно скопируйте в буфер обмена SQL-программу запроса, чтобы была возможность вернуться к ее первоначальному варианту) и просмотрите полученные после редактирования результаты.
На основе запроса «Контракты» создайте в Режиме SQL программу запроса выборки из таблицы «Контракты» полей «Товар», «Клиент»,
«Количество» с условием по количеству > 100, группировкой по полю «Клиент» и сортировкой по полю «Товар».
Контрольные вопросы
1. Назначение запросов.
2. Типы запросов.
3. Процедура создания простого запроса с помощью Мастера.
4. Структура QBE-бланка Конструктора.
5. Создание запроса на выборку по условию.
6. Создание сложного запроса со связками «и» и «или».
7. Структура Построителя выражений.
8. Назначение и процедура создания запроса с параметром.
9. Назначение и процедура создания запроса с вычисляемым полем.
10. Способы переименования полей в таблице запроса.
11. Назначение и процедура создания итогового запроса.
12. Назначение функций групповых операций.
13. Назначение и процедура создания перекрестного запроса
14. Режим SQL для создания запросов.
15. Назначение операторов SELECT, FROM, WHERE в программе запроса.
16. Назначение операторов ORDER BY, GROUP BY, TRANSFORM в программе запроса.
