Реализация запросов к базе данных
Стоимость проживания. Создадим в режиме конструктора запрос для вычисления стоимости проживания гостей в номерах.
Динамическая таблица должна содержать поля «ФИО» и «Номер паспорта» таблицы «Гости», поля «Номер комнаты», «Дата приезда» и «Дата отъезда» таблицы «Заселение», а также вычисляемое поле «Стоимость проживания».
Чтобы создать этот запрос сначала необходимо нажать кнопку «Запросы» на панели «Объекты» окна базы данных, затем кнопку «Создание запроса в режиме конструктора». Далее щелчком мыши выделяем таблицы «Гости», «Заселение» и «Номера» и нажмем кнопку «Добавить», затем кнопку «Закрыть». Затем в бланк запроса добавляем все описанные выше поля таблиц. Также добавим новое поле с помощью построителя выражений:
«Стоимость проживания: ([Дата отъезда]-[Дата приезда])*[Номера]![Стоимость за место в сутки]»
Запрос в режиме конструктора представлен на рисунке 12.
Рисунок 12 – Запрос «Стоимость проживания» в режиме конструктора
Результат запроса в режиме таблицы представлен на рисунке 13.
Рисунок 13 – Запрос «Стоимость проживания» в режиме таблицы
Текст запроса «Стоимость проживания» на языке SQL:
SELECT Гости.ФИО, Гости.[Номер паспорта], Заселение.[Номер комнаты], Заселение.[Дата приезда], Заселение.[Дата отъезда], ([Дата отъезда]-[Дата приезда])*[Номера]![Стоимость за место в сутки] AS [Стоимость проживания]
FROM Номера INNER JOIN (Гости INNER JOIN Заселение ON Гости.[Номер паспорта] = Заселение.[Номер паспорта]) ON Номера.[Номер комнаты] = Заселение.[Номер комнаты];
Гости, проживающие на дату. Создадим в режиме конструктора запрос с параметром для вывода списка гостей, проживающих в гостинице на требуемую дату.
Динамическая таблица должна содержать поля «ФИО» и «Номер паспорта» таблицы «Гости», поля «Номер комнаты», «Дата приезда» и «Дата отъезда» таблицы «Заселение».
Чтобы вывести на экран только список гостей, проживающих в гостинице на требуемую дату, в строке «Условие отбора» для поля «Дата приезда» введем выражение «<=[Введите дату]», а для поля «Дата отъезда» введем выражение «>=[Введите дату]». Запрос в режиме конструктора представлен на рисунке 14.
Запрос в режиме конструктора представлен на рисунке 14.
Рисунок 14 – Запрос «Гости, проживающие на дату» в режиме конструктора
Результат запроса в режиме таблицы представлен на рисунке 13.
Рисунок 15 – Запрос «Гости, проживающие на дату» в режиме таблицы
Текст запроса «Гости, проживающие на дату» на языке SQL:
SELECT Гости.ФИО, Гости.[Номер паспорта], Заселение.[Номер комнаты], Заселение.[Дата приезда], Заселение.[Дата отъезда]
FROM Гости INNER JOIN Заселение ON Гости.[Номер паспорта] = Заселение.[Номер паспорта]
WHERE (((Заселение.[Дата приезда])<=[Введите дату]) AND ((Заселение.[Дата отъезда])>=[Введите дату]));
Расчет занятости комнат. Создадим в режиме конструктора запрос на создание новой таблицы, содержащей данные о текущей занятости номеров.
При создании запроса в режиме конструкторе выберем тип запроса «Создание таблицы». Имя создаваемой таблицы введем «Занятость комнат».
Новая таблица должна содержать поля «Номер комнаты» и «Количество мест» таблицы «Номера» а также вычисляемые поля «Занято» и «Занятость».
Запрос в режиме конструктора представлен на рисунке 16.
Рисунок 16 – Запрос «Расчет занятости комнат» в режиме конструктора
Результат выполнения запроса представлен на рисунке 17.
Рисунок 17 – Созданная таблица «Занятость комнат»
Текст запроса «Расчет занятости комнат» на языке SQL:
SELECT Номера.[Номер комнаты], Номера.[Количество мест], Count([Заселение]![№ п/п]) AS Занято, IIf([Занято]=0,"Свободен",IIf([Количество мест]=[Занято],"Занято","Частично")) AS Занятость INTO [Занятость комнат]
FROM Номера LEFT JOIN Заселение ON Номера.[Номер комнаты] = Заселение.[Номер комнаты]
GROUP BY Номера.[Номер комнаты], Номера.[Количество мест];
Обновить занятость. Создадим в режиме конструктора запрос на обновление данных о занятости таблицы «Номера» на основе данных таблицы «Занятость номеров».
При создании запроса в режиме конструкторе выберем тип запроса «Обновление». Добавим таблицы «Номера» и «Занятость комнат».
Выберем поле «Занятость» таблицы «Номера». Для обновления поля в строке «Обновление» для поля «Занятость» введем выражение «[Занятость комнат]![Занятость]».
Запрос в режиме конструктора представлен на рисунке 18.
Рисунок 18 – Запрос «Обновить занятость» в режиме конструктора
Результат выполнения запроса представлен на рисунке 19.
Рисунок 19 – Обновленная таблица «Номера»
Текст запроса «Обновить занятость» на языке SQL:
UPDATE [Занятость комнат] INNER JOIN Номера ON [Занятость комнат].[Номер комнаты] = Номера.[Номер комнаты] SET Номера.Занятость = [Занятость комнат]![Занятость];
Количество дней занятости. Создадим запрос для вычисления общего числа дней занятости каждого номера.
Динамическая таблица должна содержать поле «Номер комнаты» таблицы «Номера», поле «Категория» таблицы «Категории номеров» и вычисляемое поле «Количество дней занятости».
Добавим вычисляемое поле с помощью построителя выражений:
«Количество дней занятости: [Заселение]![Дата отъезда]-[Заселение]![Дата приезда]»
Включим отображение строки «Групповая операция» в бланке запроса. Теперь укажем, что для каждого номера должно быть вычислено общее число дней занятости. Для этого в строке «Групповая операция» бланка запроса для поля «Количество дней занятости» выберем из списка функцию Sum.
Запрос в режиме конструктора представлен на рисунке 20.
Рисунок 20 – Запрос «Количество дней занятости» в режиме конструктора
Результат запроса в режиме таблицы представлен на рисунке 21.
Рисунок 21 – Запрос «Количество дней занятости» в режиме таблицы
Текст запроса «Количество дней занятости» на языке SQL:
SELECT Номера.[Номер комнаты], [Категории номеров].Категория, Sum([Заселение]![Дата отъезда]-[Заселение]![Дата приезда]) AS [Количество дней занятости]
FROM [Категории номеров] INNER JOIN (Номера INNER JOIN Заселение ON Номера.[Номер комнаты] = Заселение.[Номер комнаты]) ON [Категории номеров].[Код категории] = Номера.[Код категории]
GROUP BY Номера.[Номер комнаты], [Категории номеров].Категория;
Количество и стоимость мест по категориям. Создадим запрос для вычисления общего числа мест и общей стоимости проживания в номерах каждой категории.
В запрос добавим таблицы «Категории номеров», «Номера» и «Заселение».
Динамическая таблица должна содержать поле «Категория» таблицы «Категории номеров», поле «Количество мест» таблицы «Номера» и вычисляемое поле «Общая стоимость».
Добавим вычисляемое поле с помощью построителя выражений:
«Общая стоимость: (Заселение![Дата отъезда]-Заселение![Дата приезда])*[Номера]![Стоимость за место в сутки]»
Включим отображение строки «Групповая операция» в бланке запроса. Теперь укажем, что для каждого номера должно быть вычислено общее число дней занятости. Для этого в строке «Групповая операция» бланка запроса для поля «Количество мест» и «Общая стоимость» выберем из списка функцию Sum.
Запрос в режиме конструктора представлен на рисунке 22.
Рисунок 22 – Запрос «Количество и стоимость мест по категориям» в режиме конструктора
Результат запроса в режиме таблицы представлен на рисунке 23.
Рисунок 23 – Запрос «Количество и стоимость мест по категориям» в режиме таблицы
Текст запроса «Количество и стоимость мест по категориям» на языке SQL:
SELECT [Категории номеров].Категория, Sum(Номера.[Количество мест]) AS [Общее количество мест], Sum((Заселение![Дата отъезда]-Заселение![Дата приезда])*[Номера]![Стоимость за место в сутки]) AS [Общая стоимость]
FROM ([Категории номеров] INNER JOIN Номера ON [Категории номеров].[Код категории] = Номера.[Код категории]) INNER JOIN Заселение ON Номера.[Номер комнаты] = Заселение.[Номер комнаты]
GROUP BY [Категории номеров].Категория;
Число дней проживания. Теперь создадим перекрестный запрос для подсчета общего количества дней проживания каждым гостем в каждом номере.
В режиме конструктора выделим таблицы «Номера», «Заселение» и «Гости». Добавим в бланк запроса поле «ФИО» из таблицы «Гости» и поле «Номер комнаты» из таблицы «Заселение».
Добавим вычисляемые поля с помощью построителя выражений:
«Дней проживания: [Заселение]![Дата отъезда]-[Заселение]![Дата приезда]»
«Общее число дней проживания: [Заселение]![Дата отъезда]-[Заселение]![Дата приезда]»
Теперь определим тип создаваемого запроса как «Перекрестный». Далее укажем, что значения поля «ФИО» будут использоваться в качестве заголовков строк, выбрав в строке «Перекрестная таблица» бланка запроса для этого поля из списка «Заголовки строк». Затем укажем, что значения поля «Номер комнаты» будут использоваться в качестве заголовков столбцов, выбрав в строке «Перекрестная таблица» бланка запроса для этого поля из списка «Заголовки столбцов». Теперь нужно указать, что поле «Дней проживания» содержит значения, на основании которых будет вычисляться суммарное количество дней проживания. Для этого нужно в строке «Перекрестная таблица» бланка запроса для поля «Дней проживания» выберем из списка «Значение», а далее в строке «Групповая операция» бланка запроса для поля «Дней проживания» выберем из списка функцию Sum. В строке «Перекрестная таблица» бланка запроса для поля «Общее число дней проживания» выберем из списка «Заголовки строк», а затем в строке «Групповая операция» бланка запроса для этого поля выберите из списка функцию Sum.
Запрос в режиме конструктора представлен на рисунке 24.
Рисунок 24 – Запрос «Число дней проживания» в режиме конструктора
Результат запроса в режиме таблицы представлен на рисунке 25.
Рисунок 25 – Запрос «Число дней проживания» в режиме таблицы
Текст запроса «Число дней проживания» на языке SQL:
TRANSFORM Sum([Заселение]![Дата отъезда]-[Заселение]![Дата приезда]) AS [Дней проживания]
SELECT Гости.ФИО, Sum([Заселение]![Дата отъезда]-[Заселение]![Дата приезда]) AS [Общее число дней проживания]
FROM Номера INNER JOIN (Гости INNER JOIN Заселение ON Гости.[Номер паспорта] = Заселение.[Номер паспорта]) ON Номера.[Номер комнаты] = Заселение.[Номер комнаты]
GROUP BY Гости.ФИО
PIVOT Заселение.[Номер комнаты];