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

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

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

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

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

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

■ Запрос. Создать. Перекрестный запрос. ОК

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

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

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

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

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

Расчеты с поставщиками- перекрестный
№ п-ка	Итоговое значение Итого	Renault Clio	Renault Kangoo	Renault Laguna	Renault Logan	Renault Megane	Renault Modus	Renault Symbol
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р.