Реализация запросов

Основная задача запросов – просмотр, редактирование и анализ данных. Кроме того, запросы могут выступать источниками записей для отчетов, форм и страниц доступа. MS Access поддерживает работу со следующими типами запросов:
запросы на выборку – наиболее распространенный вид запросов. Результатом такого запроса являются данные одной или нескольких таблиц, которые отображаются в итоговой таблице. Также данные запросы могут применяться с целью группировки и вычисления различных параметров, например, суммы, среднего значения и т.п.;
запросы с параметрами – запросы,требующие ручного ввода данных, например, какого-либо условия или ограничения, необходимого для фильтрации. Кроме того, запросы с параметрами могут содержать запрос на ввод сразу нескольких данных, что удобно при работе с временными интервалами;
перекрестные запросы – предназначены для представления данных в структуре, упрощающей анализ. Такие запросы также позволяют вычислять различные параметры, но при этом они группируют результаты по двум наборам данных, определяющим соответственно строки и столбцы результирующей таблицы;
запросы на изменение – запросы, в результате которых происходит редактирование или перемещение записей. Запросы на изменение бывают нескольких видов:
добавление записи;
удаление записи;
обновление записи;
создание таблицы;
SQL-запрос – запрос, написанный на языке SQL. Используется при создании запросов, а также для обновления и управления реляционными базами данных, такими как базы данных Microsoft Access [7].
Наиболее удобно создавать запросы с помощью мастеров запросов, а затем редактировать их в конструкторе запросов.
2.2.1. Создание запросов с помощью QBE
Рассмотрим создание нескольких запросов.
1. Представление полей «Фамилия», «Имя», «Отчество» таблицы «Клиенты» в одном поле «ФИО».
Для этого используется Конструктор запросов (бланк QBE) с нужными полями и условием отбора, представленным на рисунке 9.
Рисунок 9 – Запрос «Клиенты1»
В столбце «ФИО» используется следующее выражение:
ФИО: [Клиенты]![Фамилия] & ' ' & [Клиенты]![Имя] & ' ' & [Клиенты]![Отчество]
Результат выполнения данного запроса изображен на рисунке 10.
Рисунок 10 – Результат работы запроса «Клиенты1»
2. Выбор только тех договоров из таблицы «Договоры», которые заключены в марте, с продолжительностью тура больше 15 дней.
В режиме конструктора из таблиц выбираются нужные поля и устанавливаются условия отбора, изображенные на рисунке 11.
Рисунок 11 – Запрос «Условие»
В качестве условия отбора для поля «Дата заключения договора» используется следующее условие:
Between #01.03.2015# And #31.03.2015#
Условие отбора для поля «Количество дней пребывания»: 15
Результат выполнения запроса изображен на рисунке 12.
Рисунок 12 – Результат работы запроса «Условие»
3. Вычисление полной стоимости путевки.
Для этого необходимо создать вычислительное поле с помощью Построителя выражений - «Полная стоимость», которое определяется следующим образом:
Полная стоимость путевки: [Маршруты]![Стоимость переезда] + [Договоры]![Количество дней пребывания]*[Маршруты]![Стоимость 1 дня пребывания]+[Дополнительные туристы]![Доплата, %]
Таким образом, в полную стоимость путевки входит стоимость проезда и стоимость каждого дня пребывания в туре, а также учитывается оплата за дополнительных туристов.
Еще одно необходимое поле - «Остаток оплаты» определяется следующим выражением:
Остаток оплаты: [Полная стоимость путевки]-IIf([Договоры]![Оплачено] = "75%"; [Полная стоимость путевки]*0,75;IIf([Договоры]![Оплачено]
="50%";[Полная стоимость путевки]*0,5;[Полная стоимость путевки]))
Результат выполнения данного запроса изображен на рисунке 13.
Рисунок 13 – Результат работы запроса «Оплата за путевку»
4

. Модификация запроса «оплата за путевку» включением параметрического условия отбора таким образом, чтобы условия отбора для полей «Дата заключения договора» и «Клиент» таблицы «Договоры» пользователь мог самостоятельно изменять при каждом запуске. Выполнение такого запроса должно сопровождаться выводом двух окон, необходимых для ввода пользователем конкретных параметров запроса.
После внесения изменений запрос в режиме SQL примет следующий вид:
SELECT Договоры.[Дата заключения договора], Договоры.Клиент, Договоры.[Количество дней пребывания], Маршруты.[Стоимость переезда], Маршруты.[Стоимость 1 дня пребывания], Договоры.[Код дополнительных туристов], [Маршруты]![Стоимость переезда]+[Договоры]![Количество дней пребывания]*[Маршруты]![Стоимость 1 дня пребывания]+[Дополнительные туристы]![Доплата, %] AS [Полная стоимость путевки], Договоры.Оплачено, [Полная стоимость путевки]-IIf([Договоры]![Оплачено]="75%",[Полная стоимость путевки]*0.75,IIf([Договоры]![Оплачено]="50%",[Полная стоимость путевки]*0.5 ,[Полная стоимость путевки])) AS [Остаток оплаты]
FROM Маршруты INNER JOIN ([Дополнительные туристы] INNER JOIN Договоры ON [Дополнительные туристы].[Код дополнительных туристов] = Договоры.[Код дополнительных туристов]) ON Маршруты.[Код маршрута] = Договоры.[Код маршрута]
WHERE (((Договоры.[Дата заключения договора])=[Введите дату]) AND ((Договоры.[Количество дней пребывания])=[Введите кол-во дней]));
Сохраняем созданный запрос под названием «Параметрические условия».
Результат выполнения запроса изображен на рисунке 14.
Рисунок 14 – Результат работы запроса «Параметрические условия»
5. Создание запроса с групповыми операциями. При помощи Конструктора добавляются таблицы «Маршруты» и «Дополнительные туристы». Полученный запрос для каждого клиента отдельно находит сумму дней пребывания в поездке и вычисляет среднее значение стоимости одного дня пребывания в туре. При этом окно Конструктора запросов имеет вид, изображенный на рисунке 15.
Рисунок 15 – Запрос «Параметрические условия»
Сохраняем созданный запрос под именем «Групповые операции».
Результат выполнения данного запроса изображен на рисунке 16.
Рисунок 16 – Результат работы запроса «Групповые операции»
2.2.2. Создание перекрестных запросов
Перекрестные запросы (кросс-таблицы) представляют собой двумерные таблицы, в которых отражается некоторая вычисляемая статистическая информация.
В данном случае рассматривается создание запроса, который по каждому клиенту определяет число заключенных договоров по поездке в каждую страну.
Для создания такого запроса необходимо создать базовый запрос, так как данные для перекрестного запроса берутся из разных таблиц, а базовый запрос объединяет поля этих таблиц