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

Цель

Научиться создавать и использовать запросы к данным, хранящимся в связанных таблицах базы данных.

Ввод параметров в запрос. Перекрестные запросы.

Задание 1. Ввод параметров в запрос

Мы научились конструировать запросы на выборку, а также запросы-действия, в которых можно задавать определенные условия их выполнения. Пока мы научились задавать условия отбора записей с использованием конкретных текстовых или числовых значений, например, >12. between 10 and 20, LIKE A* и так далее. Совершенно очевидно, что если значение 10 изменится и станет 50, то необходимо отрыть бланк запроса в режиме КОНСТРУКТОРА и вписать измененное значение параметра, что, конечно же, неудобно.

Эта проблема решается при использовании параметрических запросов или запросов с параметрами.

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

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

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

1. Создайте обычный запрос на выборку для таблицы ПАРТИЯ_ТОВАРА (рис.1).

2. В условие отбора для поля НАИМЕНОВАНИЕ вместо конкретного наименования товара введите скобки [ ], в которых поместите текст, поясняющий: значение какого параметра пользователь должен ввести (рис.1).

3. Выполните запрос. При этом Access должен вывести диалоговое окно с вашими пояснениями (рис.2).

4. Введите в диалоговом окне название любого товара из вашей базы данных. Результат выполнения запроса приведен на рис.3.

Рис.1. Формирование запроса с параметрами

Рис.2. Диалоговое окно в предложение ввести значение параметра

Рис.3. Результат выполнения запроса с параметрами

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

Можно использовать неограниченное количество параметров.

6. Измените запрос на поиск товаров определенного типа и определенного наименования (рис.4).

7. Диалоговые окна должны появляться в зависимости от порядка их вызова в бланке запроса (приоритет в расположении: слева направо и сверху вниз).

8. На рис.4 показан порядок вызова диалоговых окон для запроса, имеющего более одного параметра.

9. Ориентируясь на рис. 5. с примером размещения параметров, изучите последовательность вызова диалоговых окон в зависимости от расположения параметров в бланке запроса.

а). Бланк запроса в режиме КОНСТРУКТОРА

б) Порядок следования диалоговых окон

в). Результат поиска запроса с двумя параметрами

Рис.4. Использование нескольких параметров при конструировании запроса

Рис.5. Несколько параметров в запросе

Для одного поля может быть сконструирован диапазон условий поиска с использованием 2-х параметров (рис.6).

а). Использование оператора Between and с параметрами

б). Последовательно ввода параметров

в). Результат выбора значений

Рис.6. Использование нескольких параметров для формирования условия для одного поля

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

При формировании ввода значений входных параметров можно использовать символы подстановки * и ?. На рис.7. показан пример использования этих символов.

11. Используя пример на рис.7, создайте собственные запросы с символом подстановки *.

а). Формирование запроса с параметрами и символом подстановки *

б). Ввод параметров

в). Результат выполнения запроса с символом подстановки *

Рис.7. Использование символа подстановки *

12. Используя пример на рис.8, создайте собственные запросы с символом подстановки ?.

а). Формирование запроса с параметрами и символом подстановки ?

б). Ввод параметров

в). Результат выполнения запроса с символом подстановки ?

Рис.8. Использование символа подстановки ?

Задание 2. Перекрестные запросы

Перекрестный запрос является специальным запросом итогового типа.

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

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

Верхняя строка образует заголовок столбцов. Для заголовков столбцов вложенность не предусмотрена.

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

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

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

Рассмотрим создание перекрестного запроса на примере запроса о размещении партий товаров на полках склада.

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

1. Создадим запрос для данных трех таблиц: ПОЛКА, ПАРТИЯ_ТОВАРА, ТОВАР (нам нужна полная информация о полях). Пример запроса показан на рис.9.

Рис. 9. Создание перекрестного запроса

2. Измените тип запроса на Перекрестный и укажите роли полей (заголовок строк, заголовок столбца, значение). Для поля, выбранного в качестве значения, установите функцию вычисления этого значения (рис.9).

3. Запустите запрос на исполнение. Результат выполнения запроса показан на рис.10.

Рис.10. Результат выполнения перекрестного запроса

Самостоятельно

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

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

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

4. Измените созданный в п.3 запрос на запрос с параметрами, позволяющий изменять срок оплаты заказа.

5. Создайте перекрестный запрос для вычисления максимальной стоимости заказа.

6. Создайте запрос с параметрами для вычисления количество заказов, сделанных в определенном месяце.

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

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

9. Вычислите, на какую сумму не оплачены заказы за прошлый месяц.

10. Постройте перекрестный запрос о поставках каждого поставщика (схему запроса разработайте самостоятельно).