Материал: 5685

21

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

объединения записей.

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

Двойным щелчком левой клавишей мыши по связи между таблицами в схеме данных запроса открываем окно Параметры объединения. Уста-

навливаем второй способ объединения – все записи из таблицы Товар и только те записи из таблицы Поставки, в которых связанные поля совпадают (рисунок 13)

В таблице конструктора указываем поля Наименование, Код_товара из таблицы Товар и поле Код_товара (или любое другое) из таблицы Поставки. Для поля Код_товара из таблицы Поставки задаём условие отбора Is Null – незаполненные ячейки. Заполненный конструктор представлен на рисунке 14.

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

Рисунок 13 – Параметры объединения записей в запросе

Рисунок 14 – Простой многотабличный запрос с объединением записей

22

Запросы с вычисляемыми полями

1.Получите список товаров, новая стоимость которых превышает Y рублей (цены выросли на 30%). Для преобразования результата вычислений в денежный тип данных используйте функцию CCur().

2.Вычислите стоимость товаров с учётом НДС. Запрос должен содержать код и наименование товара, его цену и цену с учётом НДС. Используйте в вычисляемом поле функцию CCur().

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

4.Вычислите новую стоимость товаров поставленных после 15 марта текущего года (цены выросли на 20%).

5.Рассчитайте величину Стоимости для каждой выполненной поставки (цена каждого товара с учётом НДС). Расчётная формула:

Цена * (1 + Ставка НДС) * Количество.

6.Используя функцию Weekday(), определите наименования товаров, которые были поставлены в каждый понедельник мая и июня (данная функция преобразует дату в номер дня в неделе, счёт дней начинается с воскресенья).

Пример: Получить список товаров, новая стоимость которых превышает 500 рублей (цены выросли на 30%). В запросе использовать функцию

CCur().

Запрос однотабличный, все необходимые поля находятся в таблице

Товар.

Запускаем Конструктор запросов, добавляем таблицу в запрос.

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

=CCur([Товар]![Цена]*1,25)

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

Для создания вычисляемых полей можно использовать Построитель выражений. Он позволяет выбирать поля таблиц и запросов базы данных, автоматически создаёт ссылки на выбранные поля и размещает ссылки в рабочем окне Построителя. Кроме этого можно выбирать из списка встроенные функции MS Access. Основные этапы работы с Построителем представлены на рисунках 16 и 17.

Созданный запрос сохраняем и запускаем.

23

Рисунок 15 – Запрос с вычисляемым полем

Рисунок 16 – Построитель выражений, выбор функции

Рисунок 17 – Построитель выражений, создание выражения

24

Запросы с групповыми операциями

1.Определите, сколько поставщиков находится в каждом регионе.

2.Определите, скольких товаров нет в наличии.

3.Определите последние даты поставок для всех товаров.

4.Определите, сколько единиц каждого товара было поставлено в марте и апреле текущего года.

5.Определите, сколько поставок было выполнено поставщиком X в течение первого квартала.

6.Определите суммарную и среднюю стоимость товаров, находящихся на каждом из складов (данный запрос выполните к запросу 5 из группы Запросы с вычисляемыми полями, при необходимости до-

полните запрос 5 необходимыми полями).

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

ходимые поля находятся в таблицах Поставщики.

Запускаем Конструктор запросов, добавляем в запрос указанную таблицу. Добавляем в запрос групповые операции (итоги).

В таблице конструктора указываем поле Регион, даём указание сгруппировать записи по содержимому этого поля (групповая операция – Группировка). Указываем поле Регион ещё раз, выбираем функцию для расчёта количества записей в полученных группах (групповая операция – функция Count). Заполненный конструктор представлен на рисунке 18.

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

Рисунок 18 – Запрос с групповыми операциями

Перекрёстный запрос

1.С помощью мастера запросов создайте перекрёстный запрос на основе данных таблицы Поставки. Для этого:

25

запустите мастер командой вкладка Создание → Мастер запросов (рисунок 10);

в первом окне мастера выберите тип запроса – перекрёстный запрос, нажмите Ok;

во втором окне выберите таблицу Поставки и нажмите Далее;

в следующих окнах выберите поле Номер_поставщика, которое будет использовано в качестве заголовков строк и поле Номер_склада, которое будет использовано в качестве заголовков столбцов;

в предпоследнем окне мастера выберите поле Количество и функцию Сумма, подтвердите вычисление итогового значения для каждой строки;

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

Комбинированные запросы на выборку

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

запрос на определение количества поставок по регионам;

запрос для определения суммарного количества поставок (может быть выполнен к предыдущему запросу или к таблице Поставки);

запрос с вычисляемым полем и расчётной формулой Количество поставок по регионам / Суммарное количество поставок * 100.

2.Определите номера телефонов поставщиков выполнивших поставок на сумму, превышающую Х рублей. Подсказка – предварительно создайте промежуточный запрос на определение общей суммы поставок для каждого поставщика (данный запрос можно выполнить к за-

просу 5 из группы Запросы с вычисляемыми полями, если необходи-

мо дополните запрос 5 новыми полями).

3.Определите номер телефона контактного лица поставщика, выполнившего наибольшее количество поставок в первом квартале года.

4.Определите, название самого дорогого товара, находящегося на каждом из складов.

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