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

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

Задание 13: Получите данные помесячно об объеме продаж каждым агентом конкретных видов изделий различным клиентам по себестоимости и договорной цене.

Данную информацию можно получить с помощью запроса на выборку данных из таблиц Каталог, Агенты, Заказчики, Продажи за год.

·

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

1. Вначале выбираются таблицы, необходимые для конкретного запроса.

· В окне Добавлениетаблицы выберите с помощью кнопки Добавить таблицы: «Агенты», «Каталог», «Заказчики», «Продажи за год». После добавления всех таблиц закройте это окно.

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

· В нашем примере выбранные для отбора данных таблицы связаны между собой одно – многозначными связями (1:М), они устанавливаются в схеме данных автоматически.

3. В бланке запроса указывается:

ü в строках Поле, Имя таблицы – имена полей, используемых в запросе, и из каких таблиц они взяты;

ü в строке Вывод на экран –отмечаются поля, которые должны быть включены в результирующую таблицу;

ü в строке Сортировка - можно выбрать порядок сортировки записей результата;

ü в строке Условие отбора и строке Или - могут быть заданы условия отбора записей.

· Включите поле ФИО агента из таблицы «Агенты»в запрос. Для этого щелкните левой кнопкой мыши по полю ФИО агента в таблице «Агенты», поле автоматически включилось в запрос. Аналогично включите поля: ФИО заказчикаиз таблицы«Заказчики», Наименование товараиз таблицы«Каталог», Месяц, Проданное количествоиз таблицы«Продажи за год».

· В строке Вывод на экран отметьте, что выбранные поля будут включены в результирующую таблицу.

· Условия отбора задавать не требуется, поскольку необходима информация по всем агентам, заказчикам и продукции за весь период времени.

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

Ввод выражений в вычисляемые поля с помощью построителя выражений

· В свободном поле введите заголовок поля: Себестоимость: Далее нажмите на панели инструментов кнопку Построить. Открывается окно диалога Построителя выражений.

·

Себестоимость:([Каталог]![Цена за единицу]*[Продажи за год]![Проданное количество]).Для этого:

В левом окне выберите Таблицы/ Каталог, в среднем окне выберите Цена за единицу и нажмите кнопку Вставить.

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

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

Закройте круглую скобку. Нажмите кнопку ОК.

Окно мастера построителя автоматически закроется, а выражение будет введено в поле, названное Себестоимость.

· В следующем свободном поле введите выражение с помощью построителя выражений:

Общая договорная стоимость: ([Продажи за год]![Договорная цена за единицу]*[Продажи за год]![Проданное количество])Данное выражение означает, что значения из поля Договорная цена за единицутаблицы “Продажи за год” умножаются на значения из поляПроданное количество этой же таблицы.

· В запросе отметьте, что вычисляемые поля выводятся на экран.

· Нажмите кнопку Запускзапроса на панели инструментов и сравните полученный результат с таблицей:

· Сохраните запрос под именем: «Запрос на выборку» и закройте его.

Задание14: Осуществите выборку данных о работе конкретных агентов (агент задается пользователем) за интересующий период времени от начала года.

Данную операцию возможно осуществить с помощью параметрического запроса на выборку.

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

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

· Включите все поля таблицы Запрос на выборку в запрос.

· В условие отбора поля ФИО агента введите параметр: [введите имя агента:].А в условие отбора поля Месяц введите параметр:<=[введите конец учета:].Между условиями отбора в разных полях одной строки выполняется логическая операция AND, т.е. требуется вывести данные по конкретному агенту и за заданный период времени. Если условия отбора указаны в разных строках, то будет выполнена логическая операция OR.

Необходимозапомнить,что имя параметра запроса не должно совпадать с наименованием самого поля.Например, в условие отбора поля Месяц нельзя вводить [Месяц].

·

· Результатом выполнения данного запроса будет таблица, по структуре совпадающая с выходной таблицей по заданию 13, однако в нее будут включены записи только по агенту Никулину и с 1 по 10 месяцы включительно.

· Сохраните запрос под именем «Параметрический запрос».

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

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

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

· Создайте запрос в режиме Конструктора. В диалоговом окне Добавления таблиц добавьте запрос «Параметрический запрос». Далее из таблицы «Параметрический запрос» перенесите все поля, кроме месяца в бланк запроса.

· В свободное поле с помощью Построителя введите выражение:

Прибыль: ([Параметрический запрос]![Общая договорная стоимость]-[Параметрический запрос]![Себестоимость]).

· Установите Вывод на экран всех полей.

· Далее на панели инструментов нажмите кнопку Группировка,в запросе появится еще одна строкаГрупповые операции:

· В строке Групповые операции напротив полей введите:

ФИО агента

ФИО заказчика Группировка

Наименование товара

Проданное количество

Себестоимость Sum

Общая договорная стоимость

· Запустите запрос и сравните результат с итогом, полученным в задании 14, при этом задайте следующие значения параметров: имя агента - Никулин,конец учета: 10.

· Сохраните запрос как «Запрос группировка данных»