Чтобы настроить в запросе к базе данных рабочего стола Access ввод условий при его выполнении, создайте запрос с параметрами. Это даст возможность использовать запрос повторно, не открывая его в Конструктор для изменения условий.
Примечание: Эта статья не относится к веб-приложениям Access.
Терминология
Прежде чем перейти к описанной ниже пошаговой процедуре, полезно ознакомиться с несколькими терминами.
на выборку;
перекрестный;
на добавление;
на создание таблицы;
на обновление.
Параметр. Параметр - это часть сведений, предоставляемых запросу при его выполнении. Параметры можно использовать отдельно или в составе длинных выражений для формирования условия запроса. Параметры можно добавить в запрос любого из следующих типов:
Условия. Условия - это "фильтры", добавляемые в запрос для указания элементов, которые он должен возвратить.
Дополнительные сведения об указанных выше типах запросов см. в статье Знакомство с запросами .
Создание запроса с параметрами
Создание параметра аналогично добавлению обычного условия в запрос:
При запуске запроса текст отображается без квадратных скобок.
Введите нужное значение и нажмите кнопку ОК .
В условии можно использовать несколько параметров. Например, выражение Between [Введите дату начала:] And [Введите дату окончания:] при выполнении запроса создаст два поля.
Указание типов данных для параметра
Можно настроить параметр так, чтобы он принимал только определенный тип данных. Это особенно важно для числовых и денежных данных, а также данных даты и времени, так как в таком случае пользователи получат более содержательное сообщение об ошибке при вводе неправильного типа данных, например при вводе текста вместо денежного значения.
Примечание: Если параметр настроен на прием текстовых данных, любое введенное значение будет распознаваться как текст, а сообщение об ошибке не будет выводиться.
Чтобы задать тип данных для параметра в запросе, выполните указанные ниже действия.
Когда запрос открыт в конструкторе, на вкладке Конструктор в группе Показать или скрыть нажмите кнопку Параметры .
В диалоговом окне Параметры запроса в столбце Параметр введите текст запроса на ввод каждого из параметров, для которых требуется указать тип данных. Проверьте, соответствуют ли параметры запросам на ввод, указанным в строке Условия в бланке запроса.
В столбце Тип данных выберите тип данных для каждого параметра.
Добавление параметра в запрос на объединение
Так как запрос на объединение нельзя просмотреть в бланке запроса, действия с ним будут немного отличаться.
Дополнительные сведения о запросах на объединение см. в статье Использование запроса на объединение для просмотра объединенных результатов нескольких запросов .
Объединение параметров с помощью подстановочных знаков для большей гибкости
Как и в случае обычных условий, вы можете объединить параметры с помощью ключевого слова Like и подстановочных знаков для поиска соответствий в более широком диапазоне элементов. Предположим, что нужно запросить страну или регион и при этом сопоставить их со значениями, которые содержат строку параметра. Для этого выполните указанные ниже действия.
При выполнении запроса с параметрами в диалоговом окне выводится сообщение с квадратными скобками, без ключевого слова Like и подстановочных знаков
:
После ввода параметра запрос возвратит значения, содержащие строку параметра. Например, строка параметра us возвратит элементы, в которых поле параметра имеет значение "Австралия" или "Австрия".
Дополнительные сведения о подстановочных знаках см. в статье Использование подстановочных знаков в качестве условий .
Возврат элементов, не соответствующих параметру
Вместо возврата элементов, соответствующих параметру, можно создать запрос, возвращающий элементы, которые ему не соответствуют. Например, может потребоваться запросить год и возвратить элементы со значением года, большим чем указанное. Для этого введите оператор сравнения слева от запроса параметра в квадратных скобках, например >[Введите год:] .
Видео: использование параметров в запросах
Использовать в запросе параметр не сложнее, чем создать запрос на основе условий. Запрос можно настроить таким образом, чтобы предлагалось ввести определенное значение, такое как артикул товара, или несколько значений, например две даты. Для каждого параметра запрос выводит отдельное диалоговое окно, в котором предлагается ввести значение.
Обычный способ разработки запроса с параметром в Access
– создать параметры и установить их в условиях отбора с квадратными скобками. При запуске запроса последовательно появляются окна ввода, в которые пользователь должен ввести нужные значения. При этом нельзя вернуться к предыдущему окну и исправить ошибочно введенные данные. Пользователь должен вводить значения вручную, без выбора из списка, что тоже повышает вероятность ошибки.
Рассмотрим еще способ, при котором данные будут подставляться из формы.
Для примера возьмем . Создадим запрос, который будет отбирать абитуриентов по специальности и учебному заведению. Запускаться запрос будет из формы. На вкладке «Создание» в разделе «Формы» выберем пункт «Пустая форма».
Добавим на форму 2 поля со списком и две кнопки.
Поле со списком
На первом шаге выбираем способ получения значений:
То есть объект будет получать данные из другой таблицы или запроса.
Затем выбираем источник данных (таблицу специальностей):
Затем выберем нужные поля для помещения в поле:
Следующий шаг – сортировка. Установим по названию, но можно и по шифру.
Затем скрываем ключевой столбец
Указываем подпись к этому элементу. Затем переименовываем его, например, ПолеСпециальность.
Точно так же создаем поле со списком учебных заведений, но включаем в него два поля – учебное заведение и город:
Переименуем в ПолеУчебноеЗаведение.
Сохраним форму.
Затем создаем запрос. В конструкторе добавляем в него таблицы абитуриентов, учебных заведений, специальностей и следующие поля:
Коды можно не выводить на экран.
Поставим параметры в условие отбора. Для этого нажимаем правую кнопку мыши на ячейку «Условие отбора» в нужном поле и выбираем пункт контекстного меню «Построить». Затем выбираем значения как на рисунке:
Нажимаем Ок.
Подставленное значение:
[Формы]![Форма для отбора по специальности и Уз]![ПолеСпециальность]
Затем так же создаем параметр в поле кода учебного заведения, но выбираем другое поле со списком.
Сохраняем запрос («ПарамЗапрос»).
Возвращаемся в форму и добавляем кнопку «Выполнить запрос».
На первом шаге Мастера запросов выбираем действие: категория «Разное», действие «Выполнить запрос». Затем из списка запросов выбираем наш «ПарамЗапрос». Затем выбираем подпись или картинку для кнопки. Сохраняем кнопку.
Добавляем вторую кнопку – для выхода. С помощью мастера указываем категорию – «Работа с формой», действие «Закрыть форму».
Сохраняем форму.
После запуска формы выбираем нужные поля и запускаем запрос:
Использование окна запроса
1. Создание простого запроса
Откроем базу данных STUD, как было рассмотрено в ЛР1. Создадим запрос, выводящий информацию о студентах, у которых имеются задолженности.
Для создания запроса активизируем окно базы данных (см. ЛР1). После этого щелкнем по корешку "Запрос" и кнопке "Создать". В появившемся диалоговом окне "Создание запроса" выберите кнопку "Конструктор". Access откроет диалоговое окно "Добавление таблицы", позволяющее выбрать базовые таблицы и запросы для создаваемого запроса. Выберите таблицы "Результаты" и "Cтуденты", нажимая кнопку "Добавить", после чего закройте это окно кнопкой "Закрыть".
Открывшееся окно конструктора запросов состоит из двух частей: верхняя содержит списки полей выбранных таблиц, а нижняя - бланк QBE для создания запроса. Каждый столбец бланка описывает одно поле, участвующее в запросе.
Включение поля в запрос производится перетаскиванием его из списка полей таблиц (расположенного в верхней части экрана) в нужный столбец бланка QBE при помощи мышки. Включение всех полей таблицы происходит перетаскиванием символа "*", находящегося вверху списка полей данной таблицы в верхней части экрана.
Включите в запрос поля таблицы "Результаты":
Предмет
Номер_С
перетащив их в бланк QBE . Рассмотренным выше методом перетащите из таблицы “Студенты” поле Фамилия.
Запрос “Задолженность” будет иметь вид, как показано на рисунке.
Установка связей между таблицами производится автоматически, используя структуру связей, созданную при генерации проекта БД. Можно задавать другие варианты связи таблиц и иные типы связей (внешнее объединение, "один-к-одному", "один-ко-многим" и т.д.). Изменение связей производится в верхней части окна выбором связанного поля в описании одной таблицы и транспортировкой его при нажатой кнопке мышки на описание соответствующего поля связанной таблицы. Тип связи можно изменить, активизировав ее щелчком мышки на линии связи.
Условие для отбора нужных нам полей производится включением этого условия для данного поля в строке QBE "Условие отбора". Несколько значений отбора вводятся в одну строку, разделенные логическими условиями AND или OR, либо вводятся в последующие ячейки строки "или".
Установим для таблицы "Результаты" условие отбора студентов, у которых задолженности, для чего в столбец описания поля внесем строку "Задолженность" из таблицы "Результаты", и в строке "Условие отбора" введем:
Да
Сбросим для этого поля флажок "Показать", запрещающий вывод данного поля в выборке (т.к его не обязательно выводить).
Кроме стандартных операторов сравнения "=", "<", ">", "<=", ">=", Access поддерживает также BEETWEN, IN, LIKE.
В запросе могут присутствовать т.н. вычисляемые поля - вычисленные значения над любыми полями таблицы. Access поддерживает большое число функций и операторов, применяемых при генерации вычисляемых полей.
Чтобы просмотреть результат запроса необходимо на панели нажать кнопку:
Изменить заголовок можно также активизировав столбец с описанием поля; а затем выполнив команду "Свойства" меню "Вид", ввести в строку "Подпись поля" его название.
Строки итоговой таблицы желательно отсортировать по полю "Фамилия" таблицы "Студенты". Для этого в столбце с описанием данного поля в строке "Сортировка" выберем пункт "по возрастанию". При необходимости сортировки по нескольким полям Access сортирует данные в порядке их расположения в бланке QBE. После просмотра запроса видно, что необходимо ввести поле Имя из таблицы “Студенты”, т.к фамилии повторяютя и запрос не несет необходимой информации. Чтобы вставить поле, перетяните его в сетку QBE на место, где он должен находится и все остальные поля передвинуться на одну позицию в право.
Запустить полученный запрос на выполнение можно также командой "Выполнить" меню "Запрос". Проверим полученные результаты на соответствие критериям отбора. Сохраним полученный запрос под именем "Задолженности". Запрос представляет собой таблицу:
Создадим запрос, в котором будет вычисляться дополнительное поле. Кафедра вычислительной техники решила своим работникам к новому году выплатить премию в размере 10% от зарплаты. Создадим запрос на базе таблицы “Преподаватели”, с полями Фамилия, Кафедра, Должность, Зарплата. Чтобы создать вычисляемое поле “Премия”, выводящее информацию о величине премии работнику, в новом столбце (следующим за заполненным), в строке “Поле” введем выражение Зарплата*0,1. Изменим заголовок поля, введя перед выражение его название. Ячейка будет содержать: «Премия: [Зарплата]*0,1».
Изменить заголовок можно также активизировав столбец с описанием поля, а затем выполнив команду «Свойства» меню «Вид», ввести в строку подпись поля его название. Введите в строку «условие отбора» для кафедры «Вычислительная техника».
1.2. Создание итогового запроса
Итоговые запросы позволяют получать статистическую информацию по группам данных, как например: сумму, среднее арифметическое, мат.ожидание, дисперсию значений полей таблицы; число значений полей, удовлетворяющих определенным условиям и т.д.
Создадим запрос среднего бала студента. Откроем новый бланк запроса, внесем в него таблицу "Студенты", “Результаты” и установим вывод полей "Номер_С", "Фамилия", "Оценка_Т", "Оценка_П", “Оценка_Л”.
Для вычисления в запросе итоговых значений щелкнем по кнопке "Групповые операции" панели инструментов. В бланке QBE появится строка "Групповая операция", где для каждого описанного поля в этой строке установлено по умолчанию "Группировка". Теперь записи по каждому полю будут группироваться, но итог подводиться не будет. Для выведения итоговых значений необходимо вместо слова "Группировка" установить какую-нибудь групповую операцию (см прил.1).
Для полей “Оценка_Т”, “Оценка_П”, “Оценка_Л” операцию "Avg", выводящую среднее значение поля для группы записей (получим средний бал).
Изменим также заголовки полей выводимой таблицы. Для этого в описании полей в строке "Поле" введем их новые названия, отделяя их от описания двоеточием:
Оценка_Т: Теория
Оценка_П: Практика
Оценка_Л: Лабы
Часть выражения, расположенная до двоеточия выводится после запуска запроса как заголовок столбца, вместо выводимого Access по умолчанию. Таким способом можно формировать более понятные заголовки полей, чем генерируемые базой.
Сохраним полученный запрос под именем "Итог". Запустите запрос и проанализируйте полученные результаты.
Встречаются задачи, в которых параметры запросов заранее неизвестны, или есть необходимость в их изменении. Для этого в Access предусмотрена возможность ввода параметров запросов непосредственно перед их исполнением. Чтобы установить ввод параметра вместо конкретного значения, в строке "Условие отбора" вводятся имя или фраза, заключенные в квадратные скобки. Все, введенное в квадратных скобках Access рассматривает как имя параметра, и выводится в диалоговом окне запроса как комментарий в вводимому параметру. В запросе можно использовать несколько параметров, но с уникальными именами.
Создадим запрос, выводящий список преподавателей работающих на задаваемой пользователем кафедре. За основу возьмем таблицу "Преподаватели". Выберем в бланк вывода пункт "Преподаватели.*", выводящий все поля таблицы "Преподаватели”. Введем еще раз описание поля “Кафедра”, сбросим для него флажок вывода на экран, и в строке "Условие отбора" этого поля введем выражение:
=[Введите название кафедры]
Сохраним полученный запрос как "Выборка преподавателей по кафедрам". Выполнив запуск запроса, мы увидим диалоговое окно, запрашивающее название кафедры.
После ввода параметра Access выводит список преподавателей, местом работы которых является запрашиваемая кафедра.
Ввод параметров можно использовать при любом типе запросов: итоговом, перекрестном, запросе-действии и т.д.
1.4. Создание перекрестного запроса
Access поддеpживает специальный тип итоговых запpосов, называемый перекрестным. Перекрестный запрос выводит вычисленные значения как функцию от существующих полей. Так в Access можно вывести запрос, описывающий суммарный зароботок по должностям.
Необходимое требование к созданию перекрестного запроса наличие поля - заголовка строки, поля - заголовка столбца, и одно поле вычисляемого значения. Поля, являющиеся заголовками строк и столбцов должны иметь в строке "Групповая операция" установку "Группировка". Для поля - значения необходимо выбрать в "Групповой операции" какую-либо групповую функцию, или выражение, содержащее групповую операцию.
Для нашего запроса для таблицы "Преподаватели" в меню "Запрос" установим указатель "Перекрестный". Access добавит в блок QBE строку "Перекрестная таблица". Затем в бланк перенесем поле "Должность", установим “заголовки столбцов”.
Следующее поле-"Кафедра”, установим “заголовки строк”
Результирующее поле- “Зарплата” , для него установим “значение”
"групповая операция" - Avg,
Сохраним полученный запрос под имеем "Зарплаты по должностям".
1.5. Создание запроса типа "внешнее объединение"
Еще одним типом специальных запросов является внешнее объединение, которое применяется в многотабличных запросах. При использовании запросов данного типа итоговый набор записей формируется не только при условии совпадения связанных полей базовых таблиц, но и из записей одной таблицы, не имеющих соответствия в другой.
Создадим запрос, выводящий информацию обо всех студентах, с задолженностями и без. В этом запросе будет использоваться внешнее объединение таблиц "Студенты" и "Результаты" по совпадению поля "Номер_С" со всеми записями таблицы "Студенты".
Для создания внешнего объединения необходимо изменить параметры объединения. Откроем запрос "Задолженность" в режиме конструктора. Щелкнем по линии связи между таблицами "Студенты" и "Результаты" для открытия диалогового окна "Параметры объединения".
По умолчанию в этом окне установлен тип объединения "выведение только тех записей, для которых совпадают связанные поля". Другие типы объединение позволяют выводить "все записи главной таблицы и только те записи дочерней, для которых совпадают значения связанных полей" и "все записи дочерней и только те записи главной, для которых связанные поля совпадают".
Выберем третий тип связи и щелкнем на кнопке "Ок". После этого на линии соединения таблиц появится стрелка, указывающая на внешнее объединение. Сохраним модифицированный запрос (через “Сохранить как” ("Save as.."), чтобы не затереть предыдущий запрос) под именем "Внешнее объединение по студентам".
Запрос с параметрами - это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести данные, например условие для возвращения записей или значение, которое требуется вставить в поле. Можно разработатьзапрос, выводящий приглашение на ввод нескольких единиц данных, например двух дат. Затем Microsoft Access может вернуть все записи, приходящиеся на интервал времени между этими датами.
Запросы с параметрами работают по двум вариантам:
· отбор данных происходит в отдельном диалоговом окне. Такие диалоговые окна существуют в любой справочной системе, где вводится вопрос, и программа ищет ответ;
· отбор данных ведется непосредственно в таблице запроса в режиме Конструктора. В строку Условие отборазаписываются критерии отбора, и только после этого на экран выводится табличная форма запроса.
1. Диалоговое окно для запроса
Диалоговые окна нужны не только для справки. Они выводят на экран только запрашиваемые данные и скрывают всю остальную информацию. Такой порядок позволяет разделить работу по нескольким направлениям и скрыть коммерческий характер информации.
Любой запрос начинается с создания простого запроса, а так как предстоит дополнительная работа, то создавать его лучше в режиме Конструктора.
После отбора таблиц и полей в том же режиме в строке Условие отборав поле, где необходимо произвести отбор, надо указать критерии отбора. Эти критерии записываются в квадратных скобках в виде формулы:
[введите:КодТовара].
После слова «введите» указывается название поля (рис. 18).
Рис. 18. Создание запроса с параметром
При проверке или запуске запроса открывается диалоговое окно Введите значение параметра (рис. 19), в котором пользователю предлагают ввести параметры. Какой параметр внести, указано на сером фоне окна, а в текстовом окне мигает курсор, дожидаясь ввода данных с клавиатуры.
Рис. 19. Диалоговое окно Введите значение параметра
После ввода параметра в диалоговом окне Введите значение параметра нажмите ОК, и на экране появится таблица с выбранными параметрами (рис. 20).
Рис. 20. Результат действия запроса с параметром
При выполнении запроса по двум и более параметрам окна Введите значение параметра будут появляться после введения каждого параметра друг за другом до тех пор, пока не будут заполнены все требуемые значения. Только после этого откроется таблица запроса.
2. Отбор данных в таблице запроса
Запросы с отбором данных считаются рабочими. Все начинается с простого запроса. В режиме Конструкторав строке Условие отбора в столбце, где необходимо произвести отбор, указываются критерии отбора. В этом случае они записываются в кавычках с указанием данных из записи или с помощью математических знаков (например, "тетради", <5)..
Итоговые запросы
В данном виде запросов выполняются математические итоговые вычисления или статистические расчеты.
При выполнении итогового запроса записи следует сгруппировать по какому-либо признаку (название товара, дата продажи и т.д.).
Для каждой из групп можно провести итоговое вычисление {например, определить общее количество поставленных тетрадей или карандашей).
Запрос начинается с составления простого запроса, то есть отбираются необходимые поля для проведения итоговой операции.
Группировка данных происходит в режиме Конструктора нажатием на кнопку (Групповые операции) на панели инструментов или выполнением команды меню Вид =>Групповые операции. В нижней части окна построения запроса появится новая строка Групповые операции, а в зоне каждого поля автоматически будет установлено значение Группировка.
В тех полях, где нужно сгруппировать данные, необходимо оставить запись Группировка. В этом случае одинаковые данные будут выведены на экран единой записью без повторения. В остальных полях, по которым следует провести итоговые вычисления, в строке Группировка нужно раскрыть список и выбрать одну из итоговых функций (рис. 21)
Рис. 21. Создание итогового запроса
Функции в раскрывающемся списке имеют краткое обозначение (табл. 11).
Таблица 11 Функции групповых операций.
| Элемент | Результат | Тип поля |
| Sum | Сумма значений поля. | «Числовой». «Дата/время». «Денежный» и «Счетчик» |
| Avg | Среднее от значений поля. | «Числовой». «Дата/время», «Денежный» и «Счетчик» |
| Min | Наименьшее значение поля. | |
| Мах | Наибольшее значение поля. | «Текстовый», «Числовой», «Дата/время»,«Денежный» |
| Count | Число значений поля без учета пустых значений. | «Текстовый», «Числовой», «Дата/время». |
| StDev | Среднеквадратичное отклонение от среднего значения поля. | |
| Var | Дисперсия значений поля. | «Числовой», «Дата/время», «Денежный» и «Счетчик» |
| First | Значение первой записи поля | «Текстовый», «Числовой», «Дата/время», «Денежный» |
| Last | Значение последней записи поля | «Текстовый», «Числовой», «Дата/время», «Денежный» |
Можно разработать запрос, выводящий приглашение на ввод нескольких единиц данных, например двух дат. Затем Microsoft Access может вернуть все записи, приходящиеся на интервал времени между этими датами.
Пример
- Создайте запрос в режиме Конструктора на основе таблицы «Töötaja» (поля: Nimi, Perekonnanimi) и таблицы «Amet» (поле: Nimetus).
- Чтобы определить параметр запроса, введите в строку Criteria (Условие отбора) для столбца «Amet » (должность) вместо конкретного значения слово или фразу и заключите их в квадратные скобки, например Введи должность. Эта фраза будет выдаваться в виде приглашения в диалоговом окне при выполнении запроса.
3. Если вы хотите, чтобы Access проверяла данные, вводимые в качестве параметра запроса, нужно указать тип данных для этого параметра. Обычно в этом нет необходимости при работе с текстовыми полями, т.к. по умолчанию параметру присваивается тип данных Текстовый (Text)
. Если же данные в поле запроса представляют собой даты или числа, рекомендуется тип данных для параметра определять. Для этого щелкните правой кнопкой мыши на свободном поле в верхней части запроса и выберите кнопку
(Параметры)
Появляется диалоговое окно Query Parameters
(Параметры запроса).
4. В столбец Параметр (Parameter) нужно ввести название параметра точно так, как он определен в бланке запроса (легче всего это сделать путем копирования), только можно не вводить квадратные скобки. В столбце Data Type (Тип данных) выберите из раскрывающегося списка необходимый тип данных. Нажмите кнопку ОК.
5. Нажмите кнопку (Запуск) , чтобы выполнить запрос. При выполнении запроса появляется диалоговое окно Введите значение параметра (Enter Parameter Value) ,
в которое нужно ввести значение, например õpetaja. В результате выполнения запроса мы увидем только те имена и фамилии, чья должность — учитель.
В одном запросе можно ввести несколько параметров. При выполнении такого запроса для каждого из параметров будут поочередно выводиться диалоговые окна Введите значение параметра в том порядке, в котором параметры перечислены в бланке запроса.
