Запрос с параметром − это запрос, который при выполнении требует ввода с клавиатуры интересующего пользователя значения критерия отбора записей, т. е. запрашивает ввод условий отбора (параметра) при каждом запуске.
Параметрический запрос создают на основе уже существующего запроса на выборку. При этом для поля, которое предполагается использовать как параметр, в ячейку строки Условие отбора бланка запроса вводится текст-приглашение, заключенный в квадратные скобки, например,
[Введите наименование товара]
Это приглашение будет выводиться в специальном диалоговом окне при запуске запроса, но уже без квадратных скобок. Текст-приглашение должен отличаться от имени поля, но может включать его.
Для поля, которое выводит даты, можно ввести приглашения следующего вида: "Введите начальную дату:" и "Введите конечную дату:", чтобы задать границы диапазона значений. Для этого в ячейке Условие отбора соответствующего поля набирают:
Between [Введите начальную дату:] And [Введите конечную дату:];
Чтобы запросить у пользователя один или несколько символов для поиска записей, которые начинаются с этих символов или содержат их, создают запрос с параметрами, использующий оператор Like и подстановочный знак “звездочка” (*). Например, выражение
Like [Введите первый символ для поиска: ] & *
выполняет поиск слов, начинающихся с указанного символа, а выражение
Like * & [Введите любой символ для поиска: ] & *
выполняет поиск слов, которые содержат указанный символ.
Формирование условий отбора в запросах
Условия отбора для различных типов данных формируются при помощи: логических операций и операций отношения, представленных в таблице 8.1.
Таблица 8.1 −Операции для формирования условий отбора
Примеры формирования различных условий отбора представлены в таблице 8.2.
Таблица 8.2 − Формирование условий отбора
| Тип данных | Постановка задачи | Пример формирования условия отбора |
| Текстовый | Вывод записей с названием товара DDR 512 Mb | DDR 512 Mb |
| Дата/время | Вывод записей с датой 23.03.09 | #23.03.09# |
| Текстовый | Вывод записей с названием товара DDR 512 Mb или DDR 1024 Mb | DDR 512 Mb orDDR 1024 Mb |
| Дата/время | Вывод записей с датой 23.03.09 или 24.03.09 | #23.03.09# or #24.03.09# |
| Текстовый | Вывод записей с количеством товаров между 200 и 550 | Between200 and 550 |
| Целый | Вывод записей с количеством товара в интервале (5; 15) | >5 and <15 |
| Целый | Вывод записей с количеством товара в интервале | >=10 and <=105 |
| Целый | Вывод записей с количеством товара больше 2600 | >2600 |
Примеры использования масок при формировании условий отбора записей в запросах представлены в таблице 8.3.
Таблица 8.3 − Маски в условиях отбора
Итоговые запросы
Итоговые запросы помимо выборки данных выполняют нахождение минимального, максимального значений, среднего арифметического или суммы по указанному полю.
Для создания итогового запроса необходимо выполнить следующие действия:
1 1 В окне базы данных выбрать вкладку Запросы и команду Создать .
2 2 В появившемся диалоговом окне указать команду Простой запрос.
3 3 Выбрать требуемые объекты и поля, нажать Далее .
4 4 Установить опцию Итоговый и нажать кнопку Итоги .
5 5 Указать вид итогов, которые следует вычислить. В итоговых запросах рассчитываются итоги только по числовым полям БД. Нажать последовательно кнопки ОК , Далее .
6 6 Ввести имя запроса, нажать кнопку Готово .
Как правило, запросы с параметром создаются в тех случаях, когда предполагается выполнять этот запрос многократно, изменяя лишь условия отбора. В отличие от запроса на выборку, где для каждого условия отбора создается свой запрос и все эти запросы хранятся в БД, параметрический запрос позволяет создать и хранить один единственный запрос и вводить условие отбора (значение параметра) при запуске этого запроса, каждый раз получая новый результат. В качестве параметра может быть любой текст, смысл которого определяет значение данных, которые будут выведены в запросе. Значение параметра задается в специальном диалоговом окне. В случае, когда значение выводимых данных должно быть больше или меньше указываемого значения параметра, в поле «Условие отбора» бланка запроса перед параметром, заключенным в квадратные скобки ставится соответствующий знак. Можно также создавать запрос с несколькими параметрами, которые связываются друг с другом логическими операциями И и ИЛИ. В момент запуска запроса на выполнение MS Access отобразит на экране диалоговое окно для каждого из параметров. Помимо определения параметра в бланке запроса, необходимо указать с помощью команды Запрос– Параметры соответствующий ему тип данных:
1. Откройте в режиме Конструктора окно запроса и добавьте в него таблицу. Создайте запрос, «перетащив» необходимые поля в бланк запроса и задав условие выбора.
2. В качестве условия введите параметр, заключенный в квадратные скобки (например, [Введите название] или >[Выше какого роста?]).
3. Выберите команду Запрос > Параметры.
4. В появившемся окне Параметры запроса введите без квадратных скобок параметр (для точности ввода воспользуйтесь «быстрыми» клавишами копирования и вставки из буфера обмена) и укажите соответствующий ему тип данных. Нажмите ОК.
5. Нажмите кнопку Запуск панели инструментов.
6. В появившемся окне укажите значение параметра.
7. Результат запроса будет содержать только те записи, которые удовлетворяют заданному значению параметра.
Вычисляемые поля в запросах
Запрос можно использовать для выполнения расчетов и подведения итогов из исходных таблиц. Для создания вычисляемых полей используются математические и строковые операторы. При этом Access проверяет синтаксис выражения и автоматически вставляет следующие символы:
Квадратные скобки ([...]), в них заключаются имена элементов управления;
Знаки номеров (ft), в них заключаются распознанные даты;
Кавычки (""), в них заключается текст, не содержащий пробелов или знаков пунктуации.
Поле, содержимое которого, является результатом расчета по содержимому других полей, называетсявычисляемым полем. Вычисляемое поле существует только в результирующей таблице. Общий формат вычисляемого поля выглядит так: Имя вычисляемого поля: Выражение для создания вычисляемого поля.
Например: Прибыль: [Доход]-[Расход].
Создание вычисляемого поля осуществляется путем простого ввода выражения для вычисления в ячейку «Поле» пустого столбца бланка запроса. После выполнения запроса вычисляемое поле, основанное на этом выражении, выводит на экран результат вычислений, а не само выражение.
1. В строку «Поле» пустого столбца бланка запроса введите выражение, начинающееся со знака «=» и состоящее из имен полей, записанных в квадратные скобки и какой-либо арифметической или другой операции.
2. После выполнения запроса в результирующей таблице появится новое поле с названием «Выражение!», используемым в качестве имени вычисления выражения.
3. В режиме конструктора запроса измените имя «Выражение!» на более значимое.
Для того, чтобы ввести сложные вычисления, используйте окно Построитель выражений, которое вызывается нажатием кнопки Построить панели инструментов, либо соответствующей командой контекстного меню. Построитель выражений облегчает создание выражений, позволяя выбирать его составляющие элементы (арифметические операции, встроенные функции, названия полей имеющихся в БД таблиц и запросов и т.п.) при помощи кнопок и списков.
Итоговые запросы
Запросы позволяют производить итоговые вычисления. Для этих целей в Access 97 предусмотреныстатистические функции SQL (рис. IV.6). Статистическую функцию задают в строке Групповая операция бланка запросов, которая появляется при выполнении команды Вид > Групповые операции.
| Функция SQL | Действие |
| Sum | Суммирование значений определенного поля |
| Avg | Вычисление среднего значения данных определенного поля |
| Min | Вычисление минимального значения поля |
| Max | Вычисление максимального значения поля |
| Count | Вычисление количества записей, отобранных запросом по условию |
| First | Определяется первое значение в указанном поле записей, отобранных запросом |
| Last | Определяется последнее значение в указанном поле записей, отобранных запросом |
| StDev | Вычисляется стандартное отклонение значений данного поля, для всех записей, отобранных запросом |
| Var | Вычисляется вариация значений данного поля для всех записей, отобранных запросом |
Рис. 6 Функции SQL
Для выполнения итогового запроса:
1. Находясь в режиме Конструктора запроса, выберите команду Видä Групповая операция или нажмите кнопку Групповая операция панели инструментов. В результате чего в бланке запроса появится строка «Групповая операция».
2. Для соответствующего поля выберите нужную функцию из списка «Группировка».
Перекрестный запрос
Перекрестный запрос применяется в том случае, если необходимо объединить данные в формате строк-столбцов. В качестве заголовков для столбцов при проектировании таких запросов можно указать значения некоторых полей или выражений:
1. В режиме Конструктора сформируйте запрос, добавив таблицу, которая должна лежать в его основе.
2. Выберите команду Запрос – Перекрестный. Строка запроса «Вывод на экран» в бланке запроса изменится на новую строку «Перекрестная таблица» и перед ней появится строка «Групповая операция».
3. В строке «Поле» укажите поле, значения которого в новой таблице должны появиться в виде строк; поле, значения которого в новой таблице должны появиться в виде столбцов, и поле, содержимое которого в перекрестной таблице необходимо индицировать в качестве значения. Полей, которые будут использованы в качестве заголовков может быть несколько.
4. Щелкните мышью в строке «Перекрестная таблица» и выберите соответствующие значениям данных полей опции из разворачивающегося списка.
5. Для поля, содержимое которого индицируется в качестве значений, в строке «Групповая операция» введите необходимую функцию, например, автосуммирования (Sum), определения среднего значения (Avg) или количества (Count). На основе данных перекрестного запроса можно строить диаграммы, представленные в виде формы (см. п. IV. 14.1. Создание формы).
Запрос в Access является объектом, который сохраняется в файле базы данных и может многократно повторяться. Все запросы, которые мы демонстрировали до сих пор, содержали конкретные значения дат, названий, имен и т. д. Если требуется повторить такой запрос с другими значениями в условиях отбора, его нужно открыть в режиме Конструктора, изменить условие и выполнить. Чтобы не делать многократно этих операций, можно создать запрос с параметрами. При выполнении такого запроса выдается диалоговое окно Введите значение параметра (Enter Parameter Value), в котором пользователь может ввести конкретное значение и затем получить нужный результат.
Покажем, как создавать запросы с параметрами на примере запроса "Отсортированный список товаров", который мы создавали ранее. Теперь мы с помощью этого запроса попробуем отобрать товары, поставляемые определенным поставщиком. Для этого:
1. Откройте данный запрос в режиме Конструктора.
2. Чтобы определить параметр запроса, введите в строку Условие отбора (Criteria) для столбца "Название" (CompanyName) вместо конкретного значения слово или фразу и заключите их в квадратные скобки, например [Поставщик:]. Эта фраза будет выдаваться в виде приглашения в диалоговом окне при выполнении запроса.
3. Если вы хотите, чтобы Access проверяла данные, вводимые в качестве параметра запроса, нужно указать тип данных для этого параметра. Обычно в этом нет необходимости при работе с текстовыми полями, т.к. по умолчанию параметру присваивается тип данных Текстовый (Text). Если же данные в поле запроса представляют собой даты или числа, рекомендуется тип данных для параметра определять. Для этого щелкните правой кнопкой мыши на свободном поле в верхней части запроса и выберите из контекстного меню команду Параметры (Parameters) или выполните команду меню Запрос, Параметры (Query, Parameters). Появляется диалоговое окно Параметры запроса (Query Parameters), представленное на рис. 4.31.
Рис. 4.31. Диалоговое окно Параметры запроса
4. В столбец Параметр (Parameter) нужно ввести название параметра точно так, как он определен в бланке запроса (легче всего это сделать путем копирования через буфер обмена), только можно не вводить квадратные скобки. В столбце Тип данных (Data Type) выберите из раскрывающегося списка необходимый тип данных. Нажмите кнопку ОК.
5. Нажмите кнопку Запуск (Run) на панели инструментов, чтобы выполнить запрос. При выполнении запроса появляется диалоговое окно Введите значение параметра (Input Parameter Value) (рис. 4.32), в которое нужно ввести значение, например Tokyo Traders. Результат выполнения запроса представлен на рис. 4.33. В него попадают только те товары, которые поставляются данным поставщиком.
В одном запросе можно ввести несколько параметров. При выполнении такого запроса для каждого из параметров будут поочередно выводиться диалоговые окна Введите значение параметра в том порядке, в котором параметры перечислены в бланке запроса.
Рис. 4.32. Диалоговое окно Введите значение параметра
Рис. 4.33. Результат выполнения запроса с параметром
Совет
При использовании запроса с параметрами делайте понятными их имена, т. к. именно они будут выводиться в диалоговом окне для пользователя.
Запрос в Access является объектом, который сохраняется в файле базы данных и может многократно повторяться. Все запросы, которые мы демонстрировали до сих пор, содержали конкретные значения дат, названий, имен и т. д. Если требуется повторить такой запрос с другими значениями в условиях отбора, его нужно открыть в режиме Конструктора, изменить условие и выполнить. Чтобы не делать многократно этих операций, можно создать запрос с параметрами. При выполнении такого запроса выдается диалоговое окно Введите значение параметра (Enter Parameter Value), в котором пользователь может ввести конкретное значение и затем получить нужный результат.
Покажем, как создавать запросы с параметрами на примере запроса "Отсортированный список товаров", который мы создавали ранее. Теперь мы с помощью этого запроса попробуем отобрать товары, поставляемые определенным поставщиком. Для этого:
Параметры запроса" width="441" height="261">
Рис. 4.31. Диалоговое окно Параметры запроса
В одном запросе можно ввести несколько параметров. При выполнении такого запроса для каждого из параметров будут поочередно выводиться диалоговые окна Введите значение параметра в том порядке, в котором параметры перечислены в бланке запроса.
Обычный способ разработки запроса с параметром в Access
– создать параметры и установить их в условиях отбора с квадратными скобками. При запуске запроса последовательно появляются окна ввода, в которые пользователь должен ввести нужные значения. При этом нельзя вернуться к предыдущему окну и исправить ошибочно введенные данные. Пользователь должен вводить значения вручную, без выбора из списка, что тоже повышает вероятность ошибки.
Рассмотрим еще способ, при котором данные будут подставляться из формы.
Для примера возьмем . Создадим запрос, который будет отбирать абитуриентов по специальности и учебному заведению. Запускаться запрос будет из формы. На вкладке «Создание» в разделе «Формы» выберем пункт «Пустая форма».
Добавим на форму 2 поля со списком и две кнопки.
Поле со списком
На первом шаге выбираем способ получения значений:
То есть объект будет получать данные из другой таблицы или запроса.
Затем выбираем источник данных (таблицу специальностей):
