Лабораторная работа № 9 Перекрестные запросы и групповые операции над данными

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

- поле, значения которого используются в качестве заголовков строк;

- поле, значения которого используются в качестве заголовков столбцов;

- поле, над значениями которого будут выполнены вычисления;

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

■ Откройте вкладку «Создание», «Мастер запросов», «Простой запрос», «ОК».

■ Необходимые нам данные возьмем из только что созданного запроса «Расчеты с поставщиками» (там мы создали вычисляемое поле «Итого», а вообще в качества источника данных для перекрестного запроса можно использовать как таблицы, так и запросы). Выбираем запрос «Расчеты с поставщиками». Далее.

■ Выбираем заголовки строк - переносим вправо поле «Номер поставщика».

■ Далее. Встаем мышкой на поле «Марка автомобиля» (выбираем заголовки столбцов). Далее.

■ Выбираем поле, над которым будем производить вычисления - встаем мышкой на поле «Итого», а в появившемся списке доступных функций выбираем Сумма. Далее.

■ Задаем имя запроса: Расчеты с поставщиками-перекрестный

Расчеты с поставщиками- перекрестный
№ п-ка	Итоговое значение Итого	RenaultClio	RenaultKangoo	RenaultLaguna	RenaultLogan	RenaultMegane	RenaultModus	RenaultSymbol
1	111 570,00€	15 150,00€	42 420,00€	44 000,00€				10 000,00€
2	117 116,00€				17 000,00€	48 000,00€	32 320,00€	19 796,00€

Рис 59. Перекрёстный запрос«Расчеты с поставщиками»

Групповые операции

Используются для выполнения вычислений над всеми отображенными в запросе записями. При этом доступны функции для определения суммы (Sum), среднего значения (Avg), минимального и максимального значений (Min, Max), среднеквадратичного отклонения (StDev), дисперсии (Var), первого и последнего значения (First, Last), подсчета числа значений (Count).

2. Посчитаем, сколько автомобилей каждой марки было продано за весь период работы салона.

■ Откройте вкладку «Создание», «Мастер запросов», «Простой запрос», «ОК».

■ Из списка таблиц и запросов выберите таблицу «Продажи», в столбец «Доступные поля» перенесите поле «Марка авто»;

■ «Далее». Задайте имя запроса: «Статистика продаж». Выберите пункт «Изменить макет запроса». «Готово».

■ Зайдите в закладку «Конструктор» и нажмите на «Итоги»

■ Щелкните п.к.м. в первой строке второго (пока еще пустого) столбца. Зайдите в построитель выражений. Введите: Количество:[Марка автомобиля]

■ ОК.

■ В строке «Группировка» у этого поля выберите функцию Count.

■ Запустите запрос. Работает? Сохраните и закройте.

3. Итоговую информацию из созданного сейчас запроса поместим в виде диаграммы на кнопочную форме «Меню»:

■ Заходим в режим конструктора формы «Меню»

■ На панели инструментов нажмите на кнопку «Добавить диаграмму»

■ Выбираем в качестве источника данных запрос «Статистика продаж».

■ Далее. Доступные поля: Марка и Количество. Далее.

■ Тип- гистограмма. Далее.

■ Дважды щелкните л.к.м. по выражению «Сумма_Количество», выберите «Отсутствует». ОК. Далее. Далее. Имя оставьте «Статистика продаж». Готово.

■ Перейдите в режим формы, затем снова вернитесь в конструктор. Отформатируйте вид диаграммы по вашему вкусу. Например дважды щелкните по вертикальной оси Y , выберите вкладку «Шкала», установите цену делений- 1. (Ведь количество продаж может быть только целым числом). Дважды щелкните по области диаграммы сначала л.к.м. (старайтесь не попасть ни по осям, ни по другим элементам), затем п.к.м. – выберите пункт «Параметры диаграммы», измените параметры, как вам нравится.

Рис. 60 Форма-меню со статистикой продаж

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

■ Откройте вкладку «Создание», «Мастер запросов», «Простой запрос», «ОК».

■ Из списка таблиц и запросов выберите таблицу «Консультанты», в столбец «Доступные поля» перенесите поле «ФИО»;

■ Из таблицы «Продажи» перенесите поле «Цена продажи»;

■ «Далее». Задайте имя запроса: «Работа консультантов». Выберите пункт «Изменить макет запроса». «Готово».

■ Зайдите в закладку «Конструктор» и нажмите на «Итоги»

■ Щелкните в строке «Группировка» поля «Цена продажи». Появится список выбора. Выберите из него функцию Count.

■ В первой строке поля «Цена продажи» напишите:

Количество продаж:Цена продажи

(Т.е. действия мы выполняем над полем «Цена продажи», а называться столбец теперь будет «Количество продаж», обратите внимание - пробел до и после двоеточия не ставится, надпись можно сделать и через построитель выражений)

■ Добавим в запрос третий столбец, в нем будут показываться общие суммы продаж по каждому консультанту, для этого:

■ Поставьте «галочку» в строке «Вывод на экран» у третьего столбца;

■ В первой строке напишите: Сумма продаж:Цена продажи

■ Строка «Имя таблицы» - Продажи;

■ Групповая операция - Sum:

Рис. 61 Использование групповых операций

■ Запустите запрос, должно получиться:

Работа консультантов
ФИО	Количество продаж	Сумма продаж
Иванов М.П.	1	16 000,00р.
Петров И.О.	5	79 000,00р.
Сидоров Н.К.	3	51 000,00р.