Использование параметров в запросах, формах и отчетах. Запросы в access

3. Разработка запросов в ms Access

Запрос (query) – это средство выбора необходимой информации из базы данных.

SQL-запросы – это запросы, которые составляются (программистами) из последовательности SQL-инструкций. Эти инструкции задают, что надо сделать с входным набором данных для генерации выходного набора. Все запросы Access строит на основе SQL-запросов, чтобы посмотреть их, необходимо в активном окне проектирования запроса выполнить команду Вид/SQL.

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

Создание запроса на выборку с помощью Мастера

При создании запроса необходимо определить:

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

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

В окне мастера выбрать необходимую таблицу (таблицу - источник) из опции Таблицы и запросы и выбрать поля данных (в области Доступные поля выделить поле и нажать кнопку >, поле перенесется в область Выбранные поля). Если запрос формируется на основе нескольких таблиц, необходимо повторить действия для каждой таблицы – источника.

Затем в окне Мастера надо выбрать подробный или итоговый отчет и щелкнуть на кнопке Далее. После этого необходимо задать имя запроса и выбрать один из вариантов дальнейшего действия: Открыть запрос для просмотра данных или Изменить макет запроса и нажать кнопку Готово. В результате чего получите готовый запрос.

Создание запроса на выборку с помощью Конструктора

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

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

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

В нижней части окна находится Бланк построения запроса QBE (Query by Example), в котором каждая строка выполняет определенную функцию:

Поле – указывает имена полей, которые участвуют в запросе
Имя таблицы – имя таблицы, с которой выбрано это поле
Сортировка – указывает тип сортировки
Вывод на экран – устанавливает флажок просмотра поля на экране
Условия отбора - задаются критерии поиска
Или – задаются дополнительные критерии отбора

В окне «Запрос: запрос на выборку» с помощью инструментов формируем запрос:

Выбрать таблицу – источник, из которой производится выборка записей.
Переместить имена полей с источника в Бланк запроса.
Задать принцип сортировки.
В строке вывод на экран автоматически устанавливается флажок просмотра найденной информации в поле.
В строке "Условия отбора" и в строке "Или" необходимо ввести условия ограниченного поиска – критерии поиска.

После завершения формирования запроса закрыть окно Запрос на выборку, ввести имя созданного запроса, и щелкнуть ОК и вернуться в окно базы данных.

Чтобы открыть запрос из окна базы данных, необходимо выделить имя запроса и щелкнуть кнопку Открыть, на экране появится окно запрос на выборку с требуемым именем.

Чтобы внести изменения в запрос его необходимо выбрать щелчком мыши в окне базы данных, выполнить щелчок по кнопке Конструктор, внести изменения. Сохранить запрос, повторить его выполнение.

studfiles.net

Использование параметров для ввода данных при выполнении запроса

Чтобы настроить в запросе к базе данных рабочего стола Access ввод условий при его выполнении, создайте запрос с параметрами. Это даст возможность использовать запрос повторно, не открывая его в Конструктор для изменения условий.

Примечание: Эта статья не относится к веб-приложениям Access.

Терминология

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

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

Дополнительные сведения об указанных выше типах запросов см. в статье Знакомство с запросами.

Создание запроса с параметрами

Создание параметра аналогично добавлению обычного условия в запрос:

Создайте запрос на выборку и откройте его в конструкторе.
В строке Условия поля, к которому нужно применить параметр, введите текст, который нужно отобразить в поле параметра, заключив его в квадратные скобки, например: [Введите дату начала:].
Повторите шаг 2 для каждого поля, в которое необходимо добавить параметры.

При запуске запроса текст отображается без квадратных скобок.

Введите нужное значение и нажмите кнопку ОК.

В условии можно использовать несколько параметров. Например, выражение Between [Введите дату начала:] And [Введите дату окончания:] при выполнении запроса создаст два поля.

Указание типов данных для параметра

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

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

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

Когда запрос открыт в конструкторе, на вкладке Конструктор в группе Показать или скрыть нажмите кнопку Параметры.
В диалоговом окне Параметры запроса в столбце Параметр введите текст запроса на ввод каждого из параметров, для которых требуется указать тип данных. Проверьте, соответствуют ли параметры запросам на ввод, указанным в строке Условия в бланке запроса.
В столбце Тип данных выберите тип данных для каждого параметра.

Добавление параметра в запрос на объединение

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

Откройте запрос на объединение в режиме SQL.
Добавьте к нему предложение WHERE, содержащее поля, в которые нужно добавить параметры.

Если предложение WHERE уже существует, проверьте, включены ли в него все поля, в которые нужно добавить параметры. Если нет, добавьте недостающие поля.
Введите запрос параметра в предложение WHERE, например WHERE [StartDate] = [Введите дату начала:].

Имейте в виду, что во все разделы запроса необходимо добавить одинаковые фильтры. На рисунке выше запрос содержит два раздела (разделенные ключевым словом UNION), поэтому параметр нужно добавить дважды. Но при выполнении запроса ввод данных запрашивается только один раз (предполагается, что во всех разделах указан одинаковый запрос).

Дополнительные сведения о запросах на объединение см. в статье Использование запроса на объединение для просмотра объединенных результатов нескольких запросов.

Объединение параметров с помощью подстановочных знаков для большей гибкости

Как и в случае обычных условий, вы можете объединить параметры с помощью ключевого слова Like и подстановочных знаков для поиска соответствий в более широком диапазоне элементов. Предположим, что нужно запросить страну или регион и при этом сопоставить их со значениями, которые содержат строку параметра. Для этого выполните указанные ниже действия.

Создайте запрос на выборку и откройте его в конструкторе.
В строке Условия поля, в которое нужно добавить параметр, введите Like "*"&[, текст сообщения, а затем ]&"*".

При выполнении запроса с параметрами откроется диалоговое окно с сообщением без квадратных скобок, ключевого слова Like и подстановочных знаков:

После ввода параметра запрос возвратит значения, содержащие строку параметра. Например, строка параметра us возвратит элементы, в которых поле параметра имеет значение "Австралия" или "Австрия".

Дополнительные сведения о подстановочных знаках см. в статье Использование подстановочных знаков в качестве условий.

Возврат элементов, не соответствующих параметру

Вместо возврата элементов, соответствующих параметру, можно создать запрос, возвращающий элементы, которые ему не соответствуют. Например, может потребоваться запросить год и возвратить элементы со значением года, большим чем указанное. Для этого введите оператор сравнения слева от запроса параметра в квадратных скобках, например >[Введите год:].

Видео: использование параметров в запросах

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

В этом видео подробнее рассказывается о создании параметров в запросах.

См. также

Использование параметров в запросах, формах и отчетах

support.office.com

Создание запросов в СУБД Access

Практическая работа № 5.

Тема занятия: Создание запросов.

Цель занятия: Научиться создавать все типы запросов. Научиться отбирать записи по заданному признаку.

План занятия:

1. Создание простых запросов с помощью мастера.

2. Создание запросов в режиме конструктора

3. Создание запросов на удаление.

4. Создание запросов на обновление.

Запрос – это способ отбора, изменения, добавления или удаления данных из одной или нескольких таблиц базы данных по определенным критериям. С помощью запросов можно просматривать, анализировать и изменять данные из нескольких таблиц. Они также используются в качестве источника данных для форм и отчетов.

1. Создание простых запросов с помощью мастера.

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

Рассмотрим данный вид запросов на конкретном примере.

Пример: Необходимо отобрать данные о всех клиентах проживающих в городе Симферополе.

• Запустите базу данных торгового предприятия и перейдите на вкладку Запросы.

• Нажмите на кнопку Создать, В окне Новый запрос выберите Простой запрос и нажмите ОК.

• В окне Создание запроса в поле Таблицы и запросы выберите таблицу «Клиенты». Используя кнопку перетащите все поля таблицы в область Выбранные поля. Нажмите Далее.

• В следующем окне установите переключатель на Изменить макет запроса И нажмите Готово.

• В появившейся форме найдите столбец Адрес в поле условие отбора укажите Like "Симферополь*" (символ * означает, что после указанного слова будет находиться любое сочетание символов)

• Закройте и сохраните изменение макета запроса.

• Проверьте правильность выполнения данного запроса. Переименуйте данный запрос в «Клиенты в Симферополе»

Задание: Создайте запрос позволяющий отобрать данные о товарах поставки которых не прекращены.

2. Создание запросов в режиме конструктора.

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

Рассмотрим данный вид запросов на конкретном примере.

Пример: Необходимо создать запрос с параметрами с приглашением на ввод условий отбора. В режиме конструктора необходимо создать запрос, позволяющий по фамилии вывести все заказы, которые он когда либо заказал.

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

• Нажмите на кнопку Создать, В окне Новый запрос выберите Конструктор и нажмите ОК.

• В окне Добавление таблиц выделите таблицы «Клиенты» и «Заказы» и нажмите кнопку Добавить.

• Из таблицы «Клиенты» перетащите поле Фамилия В Поле запроса

Таблица «Клиенты

• Аналогичным образом перетащите поля Код заказа, Товар, Сотрудник, Количества, Дата заказа Из таблицы «Заказы».

• Введите [введите фамилию клиента заказы которого вас интересуют] в поле Условие отбора для столбца Фамилия.

• Закройте режим конструктора и сохраните данный запрос. Назовите запрос «Заказы по фамилии клиента».

• Проверьте работу запроса.

Задание: Создайте запрос, позволяющий по коду сотрудника определить какие товары он продал.

3. Создание запросов на удаление.

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

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

Пример: Необходимо удалить записи о сотрудниках, которые были уволены из фирмы. На практике выполнение данного запроса выглядит так: администратор базы данных вводит в соответствующее поле код сотрудника, который был уволен.

• Нажмите на кнопку Создать, В окне Новый запрос выберите Конструктор и нажмите ОК.

• В окне Добавление таблиц выделите таблицу «Сотрудники» и нажмите кнопку Добавить.

• Нажмите на стрелку рядом с кнопкой Тип запроса и выберите тип запроса – Удаление.

• Перетащите символ * из таблицы «Сотрудники» в поле запроса, это действие позволит применить запрос ко всем полям таблицы.

• Перетащите поле Код сотрудника из таблицы «Сотрудники» в поле запроса. В поле условие отбора введите [введите код сотрудника, который был уволен].

• Сохраните данный запрос и сохраните его под именем «Уволить сотрудника».

• Проверьте работу запроса.

Задание: Создайте запрос позволяющий удалить из таблицы «Товары» те товары поставки, которых прекращены.

4. Создание запросов на обновление.

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

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

• Создайте новый запрос в режиме конструктора.

• Добавьте таблицу «Должности»

• Укажите тип запроса Обновление.

• Перетащите в поле запроса поля Наименование должности и Процентная ставка.

• В условие отбора для поля Наименование должности введите "продавец".

• В поле обновление для Процентной ставки введите [Должности]![Процентная ставка в %]+2,5.

• Назовите данный запрос – «Повышение зарплаты продавцам».

• Проверьте работу запроса.

Задание: Создайте запрос на обновление, который позволяет по коду товара изменить цену на этот товар на 15 процентов.

Контрольные вопросы:

Что такое запрос на выборку? Виды запросов в СУБД Access? Способы создания запросов. В чем главное отличие запросов от фильтров?

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

po-teme.com.ua

Создание запросов в СУБД MS Access. Виды запросов.

Поиск Лекций

Запрос – это средство выбора необходимой информации из базы данных. Вопрос, сформированный по отношению к базе данных, и есть запрос. Применяются два типа запросов: по образцу и структурированный язык запросов (SQL).

Запрос по образцу – средство для отыскания необходимой информации в базе данных. Он формируется не на специальном языке, а путем заполнения бланка запроса в окне Конструктора запросов.

SQL – запросы – это запросы, которые составляются (программистами) из последовательностей SQL – инструкций. Эти инструкции задают, что надо сделать с входным набором данных для генерации выходного набора.

Все запросы Access строит на основе SQL –запросов. Чтобы посмотреть их, необходимо в активном окне проектирования запроса выполнить команду Вид/SQL.

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

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

Простой

По условию

Параметрические

Итоговые

С вычисляемыми полями

Чтобы вызвать Конструктор запросов, необходимо перейти в окно базы данных. В окне база данных необходимо выбрать вкладку Запросы и дважды щелкнуть на пиктограмме Создание запроса в режиме конструктора. Появится активное окно Добавление таблицы на фоне неактивного окна «Запрос: запрос на выборку».В окне Добавление таблицы следует выбрать таблицу – источник или несколько таблиц из представленного списка таблиц, на основе которых будет проводиться выбор данных, и щелкнуть на кнопке Добавить. После этого закрыть окно Добавление таблицы, окно «Запрос: запрос на выборку» станет активным.Окно Конструктора состоит из двух частей – верхней и нижней. В верхней части окна размещается схема данных запроса, которая содержит список таблиц – источников и отражает связь между ними.В нижней части окна находится Бланк построения запроса QBE (Query by Example), в котором каждая строка выполняет определенную функцию:

1. Поле – указывает имена полей, которые участвуют в запросе

2. Имя таблицы – имя таблицы, с которой выбрано это поле

3. Сортировка – указывает тип сортировки

4. Вывод на экран – устанавливает флажок просмотра поля на экране

5. Условия отбора - задаются критерии поиска

6. Или – задаются дополнительные критерии отбора

В окне «Запрос: запрос на выборку» с помощью инструментов формируем query:

1. Выбрать таблицу – источник, из которой производится выборка записей.

2. Переместить имена полей с источника в Бланк запроса. Например, из таблицы Группы студентов отбуксировать поле Название в первое поле Бланка запросов, из таблицы Студенты отбуксировать поле Фамилии во второе поле Бланка запросов, а из таблицы Успеваемость отбуксировать поле Оценка в третье поле и из таблицы Дисциплины отбуксировать поле Название в четвертое поле Бланка запросов.

1. Задать принцип сортировки. Курсор мыши переместить в строку Сортировка для любого поля, появится кнопка открытия списка режимов сортировки: по возрастанию и по убыванию. Например, установить в поле Фамилия режим сортировки – по возрастанию.

2. В строке вывод на экран автоматически устанавливается флажок просмотра найденной информации в поле.

3. В строке "Условия" отбора и строке "Или" необходимо ввести условия ограниченного поиска – критерии поиска. Например, в поле Оценка ввести - "отл/A", т.е. отображать все фамилии студентов, которые получили оценки отл/A.

4. После завершения формирования запроса закрыть окно Запрос на выборку. Откроется окно диалога Сохранить – ответить Да (ввести имя созданного запроса, например, Образец запроса в режиме Конструктор) и щелкнуть ОК и вернуться в окно базы данных. Чтобы открыть query из окна базы данных, необходимо выделить имя запроса и щелкнуть кнопку Открыть, на экране появится окно запрос на выборку с требуемым именем.

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

1. Создать query в режиме конструктора или открыть существующий запрос в режиме конструктора, например «Образец запроса в режиме Конструктор».

2. В Бланк запроса в строке Условия отбора ввести условие отбора в виде приглашения в квадратных скобках, например [Введите фамилию]

3. Закрыть окно Запрос на выборку, на вопрос о сохранении изменения ответить – Да. Вернуться в окно базы данных, где созданный query будет выделен.

4. Выполнить query, щелкнув по кнопке: Открыть. В появившемся на экране окне диалога «Введите значение параметра» надо ввести, например фамилию студента, информацию об успеваемости которого необходимо получить, выполнить щелчок по кнопке ОК.

poisk-ru.ru

16. Создание запросов средствами MS Access 2000 » СтудИзба

Лекция 16. Создание запросов средствами MS Access 2000

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

§ соединение данных нескольких таблиц;

§ отображение только требуемых полей;

§ формирование сложных критериев отбора записей;

§ вычисления с использованием данных из исходных таблиц;

§ группировку информации по каким-либо критериям;

§ модификацию данных в таблицах.

MS Access предоставляет удобные механизмы быстрого создания запросов без особой подготовки, тогда как в других СУБД часто требуются знание языковых средств, для создания запросов. Для создания запросов MS Access предлагает следующие средства:

§ Конструктор запросов – средство конструирования запросов с использованием QBE (Query by example – запрос по образцу), требует минимальных знаний. Средство, уступающее режиму SQL.

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

§ Перекрестный запрос – позволяет создавать результирующие таблицы на основе результатов расчетов, полученных при анализе группы таблиц;

§ Запрос на повторяющиеся записи – средство создания запросов для выявления повторяющихся записей, выполнено в виде мастера.

§ Записи без подчиненных – позволяет создать запрос, отыскивающий те записи главной таблицы, которые не имеют подчиненных записей в дочерних таблицах.

Создание запросов начинается с выбора категории «Запросы» окна диалога «Базы данных». Затем, кнопкой «Создать» может быть вызвано окно диалога «Новый запрос», в котором выбирается один из вариантов создания запроса (рисунок 16.1).

Рисунок 16.1 - Диалоговое окно «Новый запрос»

Создание простых запросов с помощью мастера

Мастер запросов позволяет сформировать запрос на основе одной или нескольких таблиц с возможностью группирования (итоговый запрос), но без определения условий выборки, именно поэтому он получил название «Простой запрос».

Рисунок 16.2 - Окно мастера создания запросов

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

§ Запускается мастер создания простых запросов, например командой – Создание запроса с помощью мастера

§ в раскрывающемся списке «Таблицы и запросы» последовательно выбираются таблицы или запросы, информация из которых необходима пользователю, а затем, из списка «Доступные поля» в список «Выбранные поля» перемещаются требуемые поля (рисунок 16.2).

§ На втором шаге работы мастера определяется тип запроса: подробный или итоговый. Если выбран итоговый запрос, то необходимо определить итоговые операции над полями запроса: Max, Min, Sum, Avg или Count.

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

Создание запроса в режиме конструктора

Конструктор редко используется для создания запросов, чаще используется как средство модификации уже существующих. Конструктор запросов использует QBE (Query by example – запрос по образцу) позволяющий сформировать относительно сложные запросы на основе специального бланка, заполнение которого позволяет сформировать запрос, безусловно, этот способ уступает непосредственному использованию конструкций языка SQL, но требует значительно меньших знаний.

При необходимости можно использовать язык SQL, для этого необходимо перейти в режим SQL, переход выполняется командой контекстного меню конструктора запросов – «Режим SQL».

Окно конструктора (рисунок 16.3) разделено на две части. Верхняя часть предназначена для расположения таблиц, на основе которых формируется запрос. Добавление таблиц в запрос осуществляется командой панели инструментов - Отобразить таблицу.

Рисунок 16.3 - Окно конструктора запроса. Пример ввода условия.

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

Включение новых полей в запрос выполняется простым перетаскиванием имени поля из списков полей таблиц (верхней части конструктора) в требуемый столбец строки «Поле:», при этом имя таблицы определяется автоматически.

При необходимости сортировки по какому-либо полю в столбце этого поля устанавливается свойство «Сортировка» в значение По возрастанию или По убыванию, выбором из раскрывающегося списка.

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

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

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

Количество строк в QBE может варьироваться в зависимости от типа создаваемого запроса, например при использовании группировки появится новая строка – «Групповая операция».

Установка критериев отбора записей

Определение критериев выборки в запросах является одной из основных задач. Чтобы сформировать требуемый пользователю набор данных необходимо определить значения условий отбора в строке «Условие отбора:» QBE. В условиях отбора можно использовать логические операторы (or, and, not), операторы сравнения (<, >, <=, >=, <>, =), а также операторы Between, In и Like.

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

1. ввести всё условие в одну ячейку строки «Условие отбора:», с использованием логического оператора или (or).

2. одну часть условия указать в ячейке строки «Условие отбора:», а вторую часть условия вписать в нижнюю ячейку строки «Или:».

Оба варианта будут эквивалентными, второй вариант изображен на рисунке 16.4. Результатом этого запроса будет список студентов имеющих оценки 4 или 5.

Рисунок 16.4 - Использование логического оператора или (or).

Логическая операция и (and) используется в том случае, когда должны быть выполнены оба условия одновременно и только в этом случае запись будет включена в результирующий набор данных. Например, условие >2 and <5 даст список студентов имеющих оценки 3 и 4.

Логическая операция and может быть использована не только для одного поля, но и для нескольких полей, условия в которых должны выполниться одновременно, однако, в этом случае она используется неявно. На рисунке 16.5 показано неявное использование логической операции and. В результате такого запроса будут выданы все студенты, проживающие в городе «Уфа» и имеющие оценку 5. Фактически, если заглянуть в код SQL (в режиме SQL) то можно увидеть следующее условие:

[Студенты].[Город] = “Уфа” and [Студенты и занятия].[Балл] = “5”

Рисунок 16.5 – Пример использования логической операции и (and)

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

Оператор Between позволяет задать диапазон значений, например диапазон от 10 до 20 может быть определен следующим образом:

between 10 and 20

Оператор In позволяет использовать для сравнения список значений, указываемый в качестве аргумента. Например:

in (“первый”,”второй”,”третий”)

Оператор Like полезен для поиска образцов в текстовых полях, причем позволяет использовать шаблоны:

* - обозначает любое количество символов;

? - любой одиночный символ;

# - указывает, что в данной позиции должна быть цифра.

Например, для выбора фамилии, начинающейся с буквы П и с окончанием “ов” можно записать следующие условие:

Like П*ов

Особый подход к работе с полями типа дата. Значение даты в условиях отбора должно быть заключено в символы #. Например:

>#31.12.1996#

Если требуется выделить временные события конкретного дня, месяца или года, то можно использовать встроенные функции MS Access:

§ Day(дата) – возвращает значение дня месяца в диапазоне от 1 до 31;

§ Month(дата) – возвращает значение месяца в диапазоне от 1 до 12;

§ Year(дата) – возвращает значение года в диапазоне от 100 до 9999;

Текущую дату можно определить функцией Now(), используемую без аргументов.

Вычисляемые поля

В запросах на выборку можно определять вычисляемые поля, значения которых будут получены в результате каких-либо арифметических операций над полями участвующими в запросе. Например, если необходимо определить размер надбавки, равной 15% от оклада, то можно записать в строке «Поле» свободного столбца бланка запросов следующее выражение:

Надбавка: [ Оклад]*0.15

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

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

В верхней части окна область ввода, в которой редактируется выражение. Выражение может быть введено вручную, но обычно используются кнопки арифметических и логических операторов, расположенные под областью ввода. Нижний, средний список содержит перечень полей входящих в запрос, эти поля можно использовать в выражении. Поля добавляются двойным кликом мыши или нажатием кнопки «Вставить». пи поля полей входящих в запрос, которые можно использовать в выражении.ражений

Допустим, существует запрос «Отпуск товаров со склада», который содержит поля «КолОтп» (количество отпущенное) и «ЦОтп» (цена отпускная), требуется определить сумму продажи. Открываем запрос в режиме конструктора, переходим на пустой столбец и запускаем построитель выражений командой «Построить», контекстного меню. Находим в среднем списке имя поля «ЦОтп» и дважды щелкаем по нему мышкой. Нажимаем кнопку «*» и повторяем ту же последовательность действий для поля «КолОтп». Получаем требуемое выражение (рисунок 16.6).

Рисунок 16.6 - Окно построителя выражений

Прежде чем применить созданное выражение, рекомендуется определить имя вычисляемого поля, например «Сумма»:

Сумма: [ЦОтп]*[КолОтп]

После нажатия «ОК» полученный результат будет помещен в новый столбец бланка QBE.

Контрольные вопросы

1. Что такое запрос?

2. В чем заключаются функции запроса?

3. Какие способы создания запросов существуют в MS Access?

4. Чем отличаются возможности различных способов создания запросов?

5. Как создается запрос с помощью мастера?

6. Что представляет собой бланк запросов?

7. Как сформировать условия отбора записей?

8. Назовите основные логические операторы.

9. Что представляет собой оператор between?

10. В чем назначение оператора Like?

11. Как создаются вычисляемые поля?

12. Преимущества и недостатки конструктора запросов?

Задания для самостоятельной работы

Задание 1. Создайте запрос «Просроченные книги», по следующему описанию:

1. В окне «Базы данных» выбираем категорию «Запросы».

2. Открываем окно мастера командой «Создание запроса с помощью мастера».

3. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Книги». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Название» и «Автор».

4. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Экземпляры». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Инвентарный номер», «Дата возврата» и «Наличие».

5. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Читатели». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Номер_ЧБ», «ФИО», «Тел_дом», «Тел_раб». Нажимаем кнопку «Далее».

6. На данном этапе выбираем подробный запрос и нажимаем кнопку «Далее».

7. Указываем имя запроса Просроченные_книги и нажимаем кнопку «Готово».

8. Откроем запрос «Просроченные_книги» в режиме конструктора, нажав кнопку «Конструктор» (рисунок 16.7).

Рисунок 16.7 Запрос «Просроченные_книги» в режиме конструктора

9. В бланке запроса в строке «Условие отбора» для поля «Дата_возврата» укажем значение < Now().

10. В бланке запроса в строке «Условие отбора» для поля «Наличие» установим значение нет и снимем флажок «Вывод на экран».

11. Закроем окно конструктора.

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

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

Задание 3. Из запроса «Просроченные_книги» извлеките информацию о должниках (Номер читательского билета, ФИО, Домашний и рабочий телефоны)

Задание 4. Создайте запрос на получение списка книг одного автора.

studizba.com

Вычисляемые поля в запросах Access

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

Задача 1. В таблице ТОВАР имеются поля ЦЕНА и СТАВКА_НДС, вычислите цену с учетом НДС и сравните ее с полученной в вычисляемом поле таблицы Цена с НДС.

Создайте в режиме конструктора запрос на выборку для таблицы ТОВАР. Перетащите в бланк запроса поля НАИМ_ТОВ, ЦЕНА, СТАВКА_НДС и Цена с НДС (рис. 4.6).
Для подсчета цены с учетом НДС создайте вычисляемое поле, записав в пустой ячейке строки Поле (Field) выражение [ЦЕНА]+[ЦЕНА]*[СТАВКА_НДС].
Для отбора записей со значением выше 5000 в вычисляемом поле в строку Условие отбора (Criteria) введите > 5000
После ввода выражения система по умолчанию формирует имя вычисляемого поля Выражение 1, которое становится заголовком столбца в таблице с результатами выполнения запроса. Это имя вставится перед выражением [ЦЕНА]+[ЦЕНА]*[СТАВКА_НДС]. Для каждого нового вычисляемого поля в запросе номер выражения увеличивается на единицу. Имя вычисляемого поля отделяется от выражения двоеточием. Для изменения имени установите курсор мыши в вычисляемом поле бланка запроса и нажмите правую кнопку мыши. В контекстно-зависимом меню выберите Свойства (Properties) поля и в строку Подпись (Caption) введите новое имя поля ― Цена с НДС1. Теперь в таблице с результатами выполнения запроса в заголовке вычисляемого столбца отобразится это имя. Имя поля может быть исправлено также непосредственно в бланке запроса.
Для отображения результата выполнения запроса щелкните на кнопке Выполнить (Run) в группе Результаты (Results). Вычисляемое поле таблицы и за-проса имеют одинаковые значения.
Измените в одной из записей запроса цену товара. Значения в обоих вычисляемых полях будут моментально пересчитаны.
Для формирования сложного выражения в вычисляемом поле или условии отбора целесообразно использовать построитель выражений. Построитель позволяет выбрать необходимые в выражении имена полей из таблиц, запросов, знаки операций, функции. Удалите выражение в вычисляемом поле и используйте построитель для его формирования.
Вызовите построитель выражений (Expression Builder), нажав кнопку Построитель (Builder) в группе Настройка запроса (Query Setup) ленты Конструктор (Design), или выбрав Построить (Build) в контекстно-зависимом меню. Курсор мыши должен быть установлен предварительно в ячейке ввода выражения.
В левой части окна Построитель выражений (Expression Builder) (рис. 4.7) выберите таблицу ТОВАР, на которой построен запрос. Справа отобразится список ее полей. Последовательно выбирайте нужные поля и операторы, двойным щелчком вставляя в выражение. Выражение сформируется в верхней части окна. Обратите внимание, построитель перед именем поля указал имя таблицы, которой оно принадлежит, и отделил его от имени поля восклицательным знаком.
Завершите процесс построения выражения в вычисляемом поле, щелкнув на кнопке ОК.
Сохраните запрос под именем ― Цена с НДС и закройте его.
Выполните сохраненный запрос, выделив его в области навигации и выбрав в контекстном меню команду Открыть (Open).

Задача 2. В вычисляемых полях и условиях отбора можно использовать встроенные функции. В Access определено более 150 функций.Пусть необходимо выбрать все накладные, по которым производилась отгрузка в заданном месяце. В таблице НАКЛАДНАЯ дата отгрузки хранится в поле ДАТА_ОТГ с типом данных Дата/время (Date/Time).

Создайте в режиме конструктора запрос на выборку для таблицы НАКЛАДНАЯ. Перетащите в бланк запроса поля НОМ_НАКЛ и КОД_СК (рис. 4.8).
Создайте вычисляемое поле в пустой ячейке строки Поле (Field), записав туда одно из выражений: Format([НАКЛАДНАЯ]![ДАТА_ОТГР];»mmmm») ― эта функция возвратит пол-ное название месяцаили Format([НАКЛАДНАЯ]![ДАТА_ОТГР];»mm») ― эта функция возвратит номер месяца.
Для отбора накладных, выписанных в заданном месяце, в вычисляемом поле в строку Условие отбора (Criteria) введите название месяца, например март (рис. 4.8), или номер месяца, например 3 в соответствии с параметром в функции Format.
Выполните запрос, нажав кнопку Выполнить (Run) в группе Результаты (Results) на вкладке ленты Работа с запросами | Конструктор (Query Tools | Design).
Запишите в вычисляемом поле функцию Month(НАКЛАДНАЯ!ДАТА_ОТГ), и убедитесь, что эта функция возвращает выделенный из даты номер месяца.
Для выборки всех строк, относящихся ко второму кварталу, в строку Условие отбора (Criteria) введите оператор Between 4 And 6, определяющий, попадает ли значение выражения в указанный интервал.
Запишите в вычисляемом поле выражение MonthName(Month(НАКЛАДНАЯ!ДАТА_ОТГ)) и убедитесь, что функция MonthName преобразует номер месяца в его полное на-звание.

Для закрепления смотрим видеоурок:

Параметрический запрос Access тут.

accesshelp.ru

Использование параметров в запросах, формах и отчетах

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

Если вам необходимо, чтобы запрос в Access при запуске каждый раз предлагал ввести параметры, создайте запрос с параметрами.

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

В этой статье

Обзор

Использование параметров в запросах

Указание типов данных для параметра

Создание формы для ввода параметров

Создание формы для ввода параметров для отчета

Обзор

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

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

Использование параметров в запросах

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

Добавление параметра в запрос

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

[Дата начала]

При выполнении запроса с параметрами этот текст отобразится в диалоговом окне без квадратных скобок.

При запросе параметров также можно использовать выражения. Пример:

Between [Дата начала] And [Дата окончания]

Примечание: Для каждого параметра выводится отдельное диалоговое окно. Во втором примере появляется два окна: одно для даты начала, второе — для даты окончания.
Повторите шаг 2 для каждого поля, для которого хотите добавить параметры.

С помощью описанных выше действий можно добавить параметр в запрос любого из следующих типов: выборка, добавление, создание таблицы и обновление, а также перекрестный запрос.

Кроме того, с помощью перечисленных ниже действий можно добавить параметр в запрос на объединение.

Откройте запрос на объединение в режиме SQL.
Добавьте предложение WHERE, содержащее каждое поле, для которого требуется ввести параметр.

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

Указание типов данных для параметра

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

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

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

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

Создание формы для ввода параметров

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

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

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

Создание формы для ввода параметров для отчета

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

Шаг 1. Создайте форму для ввода значений

Шаг 2. Создайте блок кода, проверяющий, загружена ли форма параметров

Шаг 3. Создайте макрос, управляющий работой формы и отчета

Шаг 4. Добавьте на форму кнопки "ОК" и "Отмена"

Шаг 5. Используйте значения из формы в качестве условий запроса

Шаг 6. Добавьте макрокоманды в события отчета

Шаг 7. Проверьте форму в действии

Шаг 1. Создайте форму для ввода значений

На вкладке Создание в группе Формы нажмите кнопку Конструктор форм.

В режиме конструктора нажмите F4, чтобы открыть страницу свойств, и задайте свойства формы, как показано в таблице ниже.

Свойство	Значение
Подпись	Введите имя, которое будет отображаться в строке заголовка формы.
Режим по умолчанию	Простая форма
Режим формы	Да
Режим таблицы	Нет
Режим сводной таблицы	Нет
Режим сводной диаграммы	Нет
Полосы прокрутки	Отсутствуют
Область выделения	Нет
Кнопки навигации	Нет
Тип границы	Окно диалога

Для каждого параметра, который будет вводиться с помощью формы, на вкладке Конструктор в группе Элементы управления нажмите кнопку Поле.

Задайте для полей свойства, как показано в таблице ниже.

Свойство	Значение
Имя	Введите имя, описывающее параметр, например ДатаНачала.
Формат	Выберите формат, который соответствует типу данных поля параметра. Например, для поля даты выберите вариант Полный формат даты.

Сохраните форму и задайте для нее название, например frmCriteria.

Шаг 2. Создайте блок кода, проверяющий, загружена ли форма параметров

На вкладке Создание в группе Макросы и код нажмите кнопку Модуль. Если вы используете Access 2007, на вкладке Создание в группе Другие нажмите кнопку Модуль.

В редакторе Visual Basic откроется новый модуль.
Вставьте в редакторе следующий код:
Function IsLoaded(ByVal strFormName As String) As BooleanDim oAccessObject As AccessObjectSet oAccessObject = CurrentProject.AllForms(strFormName)If oAccessObject.IsLoaded ThenIf oAccessObject.CurrentView <> acCurViewDesign ThenIsLoaded = TrueEnd IfEnd IfEnd Function
Сохраните модуль под уникальным именем и закройте редактор Visual Basic.

Шаг 3. Создайте макрос, управляющий работой формы и отчета

С помощью вложенных макросов Access можно задать все этапы одного макроса. Мы создадим четыре вложенных макроса, управляющих выполнением различных задач в этой процедуре: для открытия и закрытия диалогового окна, а также для кнопок "ОК" и "Отмена", Используя в качестве основы приведенный ниже снимок экрана, создайте макрос с указанными вложенными макросами и макрокомандами. Обратите внимание, что в этом примере форма для ввода параметров называется frmCriteria. Настройте макрос таким образом, чтобы его имя соответствовало названию созданной ранее формы. Для просмотра всех макрокоманд вам также потребуется нажать кнопку Показать все действия на вкладке Конструктор.

Сохраните и закройте макрос. Задайте для макроса имя, например Макрос диапазона дат.

Шаг 4. Добавьте на форму кнопки "ОК" и "Отмена"

Снова откройте форму параметров, которую создали в режиме конструктора.
На вкладке Конструктор в группе Элементы управления отмените выбор параметра Использовать мастера.
На вкладке Конструктор в группе Элементы управления щелкните Кнопка.
Расположите курсор под полями на форме и мышью создайте кнопку ОК.
Если окно свойств не отображается, откройте его, нажав клавишу F4.

Задайте для кнопки ОК свойства согласно приведенной ниже таблице.

Свойство	Значение
Имя	ОК
Подпись	ОК
По умолчанию	Да
Нажатие кнопки	Введите имя макроса, например Макрос диапазона дат.ОК.

Создайте кнопку Отмена и задайте для нее свойства, как показано в таблице ниже.

Свойство	Значение
Имя	Отмена
Подпись	Отмена
Нажатие кнопки	Введите имя макроса, например Макрос диапазона данных.Отмена.

Сохраните и закройте форму.

Шаг 5. Используйте значения из формы в качестве условий запроса

Откройте запрос, который создали в режиме конструктора.
Введите условия для данных. Используйте объект Формы, имя формы и имя элемента управления:
- Например, в базе данных Access (ACCDB или MDB) для формы frmCriteria можно задать следующее выражение, ссылающееся на элементы управления "Дата начала" и "Дата окончания" в запросе:
  
  Between [Формы]![frmCriteria]![Дата начала] And [Формы]![frmCriteria]![Дата окончания]

Шаг 6. Добавьте макрокоманды в события отчета

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

Задайте два дополнительных свойства отчета, как показано в таблице ниже.

Свойство	Значение
Открытие	Введите имя макроса, например Макрос диапазона дат.Открыть диалоговое окно.
Закрытие	Введите имя макроса, например Макрос диапазона дат.Закрыть диалоговое окно.

При открытии отчета Access будет выполнять команды, содержащиеся во вложенном макросе "Открыть диалоговое окно" объекта "Макрос диапазона дат". И аналогично: при закрытии отчета Access будет выполнять команды, содержащиеся во вложенном макросе "Закрыть диалоговое окно" объекта "Макрос диапазона дат".

Сохраните и закройте отчет.

Шаг 7. Проверьте форму в действии

Теперь, когда все объекты Access созданы, можно проверить, как они работают. Откройте отчет в режиме отчета или представлении для печати и обратите внимание, что перед показом отчета Access отображает форму ввода параметров в диалоговом режиме. Введите необходимые условия в созданные вами текстовые поля и нажмите кнопку ОК на форме. После этого Access скроет форму (вывод на экран — "Нет") и отобразит отчет, содержащий только те данные, которые соответствуют вашим условиям. Это произойдет благодаря тому, что запрос с параметрами, на котором основан отчет, использует значения в элементах управления на скрытой форме. Когда вы закроете отчет, Access также закроет форму параметров.

К началу страницы

support.office.com