Разработка перекрестного запроса

Цель работы – получение сводной информации из таблицы.

Общие сведения

Пусть имеется таблица базы данных об объеме продаж по месяцам с полями: ФИО (фамилия продавца), Месяц, Объем продаж. В эту таблицу заносятся данные о продажах. Они могут заносится ежедневно или еженедельно. Если требуется узнать текущие успехи каждого продавца, сравнить их, определить их динамику, то потребуется целый ряд запросов.

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

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

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

Следует также указать, как будет происходить обработка. Чаще всего это суммирование, но можно использовать количество значений, и среднее, и минимальное значение, и другие статистические показатели. Их список аналогичен списку возможных групповых операций, рассмотренных в предыдущей работе.

В перекрестную таблицу автоматически добавляется столбец итогов (общий объем продаж для каждого продавца).

Задание

Построить перекрестные запросы для вывода сведений:

¨ о распределении сотрудников по должностям. Строки соответствуют должностям, столбцы – полу. В ячейках таблицы выводится количество сотрудников данной категории;

¨ о распределении средних окладов по должностям. Строки соответствуют должности, столбцы – полу. В ячейках выводится сумма среднего оклада;

¨ о суммах окладов по районам для каждой должности;

¨ о сравнительном количестве мужчин и женщин, служивших и не служивших в армии. Результат должен иметь вид:

 

Пол	Служили в армии	Не служили в армии
Мужчины
Женщины

 

¨ (более сложное задание) определить величину оклад/количество детей для мужчин и женщин по каждой должности.

Порядок выполнения работы

Для создания перекрестных запросов используется Мастер. Его вызов производится с листа Запросы базы данных. Следует выбрать Создатьà Перекрестный запрос à ОК. Далее следует отвечать на вопросы Мастера, руководствуясь целью задания и приведенными выше сведениями о перекрестных запросах.

Для выполнения последнего задания следует воспользоваться тем, что запрос представляет собой виртуальную таблицу. Это означает, что можно вначале создать запрос на выборку, который будет выполнять необходимые вычисления (для данного задания – формировать в определенных полях текст Мужчины или Женщины и Служили в армии или Не служили в армии. Затем строится перекрестный запрос, исходной таблицей для которого служит первый запрос.

При открытии перекрестного запроса вначале вызывается запрос на выборку. Его результаты используются в качестве исходных данных для перекрестного запроса.

Внимание. Механизм построения запросов на базе запросов является мощным средством получения нужных данных. Его следует рекомендовать в случаях сложных вычислений. Эти вычисления можно делать по шагам, проверяя промежуточные результаты.

Результат работы

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

Создание отчета

Цель работы – представление данных в форме отчета.

Общие сведения

Для того, чтобы работать с данными, хранящимися в БД, их часто требуется распечатывать. Распечатки служат для проверки и распространения информации.

Например, экзаменационная ведомость представляет собой распечатку списка студентов заданной группы с оставленными пустыми столбцами для занесения оценок, даты сдачи экзамена и подписи преподавателя. Эта ведомость выдается преподавателю на экзамен, во время которого заполняются пустые столбцы. Далее она утверждается деканом и передается оператору ЭВМ для ввода в БД результатов экзамена.

Распечатка данных из таблицы базы данных в заданном формате называется отчетом.

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

Внимание. Как и в других приложениях MS Office, например в Word, работа с документами начинается с задания параметров страницы. В свою очередь, размер страницы и полей нельзя задать, если на компьютере не установлен принтер. Поэтому перед началом работы убедитесь в том, что в Windows установлен хотя бы один принтер. Это можно сделать через Пуск à Настройка à Принтеры.

Создание отчета

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

Пример отчета дан в Табл. 2.

Структура отчета

В структуре отчета можно выделить следующие области.

Заголовок отчета. Он находится в начале, “открывая” отчет. В него входит текст типа “Отчет о... “ и, иногда, изменяющаяся часть типа “по состоянию на.......”.

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

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

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

Таблица 2

Пример отчета

Список

работающих в фирме ОМАНИД

по состоянию на 1 января 2000 года

Таб.      номер Фамилия, имя, отчество

Должность

Оклад,  руб.

Василеостровский район

1234 Сидоров А.А.

Инженер

1000

Всего по району человек: 1

Центральный район

2345 Сидоров Б.Б.

Директор

1200 3456 Сидоров В.В.

Гл. бухгалтер

1100 4567 Сидоров Г.Г.

Бухгалтер

1000 5678 Сидоров Д.Д.

Зам. директора

1050 6789 Сидоров Е.Е.

Инженер

1000

Всего по району человек: 5

Всего работающих:

Сумма окладов:

Средний оклад:

6

6350

1058

 

 

Стр. 1

         

 

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

Группировка данных в отчете

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

В этом случае следует ввести в отчет еще две области.

Заголовок группы – область, помещаемая перед выводом каждой группы. Например:

 

Сведения по району: ………………………….

 

Примечание группы – область, завершающая группу. В ней могут находиться итоги по группе.

СУБД допускает использование вложенных групп. Так, можно сгруппировать сведения о сотрудниках по районам, а внутри каждого района – по должностям. Заголовки и итоги можно ввести для каждой группы.

Задание

Разработать простые отчеты, в которых перечисляются:

¨ табельные номера, фамилии, даты рождения, телефоны и оклады всех работников;

¨ табельные номера, фамилии, оклады и количество детей всех работников;

¨ фамилии, должности, телефоны, служба в армии всех работников.

Ввести в отчеты следующие группировки:

¨ сведения о работниках с итогами по районам (итоги см. Табл.2);

¨ сведения о работниках с общим итоговым количеством сотрудников и их детей и аналогичными итогами по районам;

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