Разработка отчетов и запросов Цель работы

Изучение возможностей и освоение приемов разработки отчетов и

запросов.

Учебные вопросы

1. Разработка отчета

2. Разработка запросов

Краткое изложение учебного материала по теме

1 Разработка отчетов

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

Печатные документы отчетности предприятий часто должны иметь точное соответствие с унифицированными формами, утвержденными Гос- комстататом РФ, как правило, Конструктор отчетов позволяет это сделать.

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

На первом шаге Мастера выбираем поля главной таблицы базы Сту- денты, которые мы хотим показать в отчете и все поля дочерней таблицы Оценки, кроме поля [№ зачетки] (рисунок 11.1).

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

На третьем шаге добавляем группировку данных по полям № инсти- тута, Курс и Группа (рисунок 11.2). Более трех уровней группировки мастер задать не позволяет, однако это можно сделать при необходимости в Кон- структоре отчетов.

Рисунок 11.1 – Выбор полей для отчета на первом шаге Мастера отчетов

 

 

Рисунок 11.2 – Добавление уровней группировки для отчета Сортировку на следующем шаге не задаем, т. к. она будет задана ав-

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

На 5-м шаге выбираем вид макета для отчета – структура, на сле- дующем задаем название отчета Студенты и их оценки и нажимаем кнопку Готово. Полученный отчет в Конструкторе отчетов показан на рисунке 11.3.

Рисунок 11.3 – Вид отчета в Конструкторе, созданного Мастером отчетов

В полученном отчете присутствуют объекты 3-х видов – Поле, которое в отчете может показывать данные поля таблицы базы, значение переменной или выражения, Надпись – любой текст в отчете и Прямоугольник – элемент оформления.

В Конструкторе отчет разбит на отдельные зоны, информация кото- рых может присутствовать в отчете один раз ( Заголовок отчета и Примеча- ние отчета), в начале каждой страницы (Верхний колонтитул) или в конце каждой страницы (Нижний колонтитул), в начале каждой группы ( Заголо- вок группы, групп может быть много) и в конце каждой группы (Примеча- ние группы) и для каждой записи главной таблицы отчета (Область дан- ных). Зоны Примечания… (их Мастер не создал) более правильно было бы назвать Итоги…, т. к. здесь можно разместить поля общих итогов (сумма, среднее и пр.) для группы или всего отчета.

Полученный отчет можно просмотреть на экране, отправить на принтер (например, с использованием соответствующих кнопок на стан- дартной панели инструментов) или в файл rtf -формата.

Созданный Мастером отчет весьма несовершенен, поля его страни- цы слишком малы, размещение полей в отчете нерационально. В отчет необходимо добавить названия факультетов, специальностей и предметов и изменить их надписи. А самое главное – необходимо задать наличие зон

Примечаний и в них разместить поле с расчетом средних оценок для всех

4- х групп.

Вначале настроим поля страницы отчета с использованием кнопки Параметры страницы вкладки Параметры страницы (верхнее 20 мм, ниж- нее 20 мм, левое 25 мм, правое 10 мм). После этого ширину отчета необхо- димо уменьшить до 17,4 см (при этом поля оценок нужно переместить вле- во и уменьшить ширину рамки в зоне Заголовок группы "Группа").

Для добавления новых и модификации существующих элементов

управления следует использовать вкладку Конструктор контекстной груп- пы вкладок Инструменты конструктора отчетов, для настройки расположе- ния объектов – вкладку Упорядочить, для настройки параметров страницы (полей и размера бумаги) – вкладку Параметры страницы (рисунок 11.4).

Рисунок 11.4 – Контекстная группа вкладок Инструменты конструктора отчетов и окно Список полей

Для использования в отчете данных справочных таблиц следует вы- брать команду Добавить поля на вкладке Конструктор (см. рисунок 11.4), в верхней части открывшегося окна Список полей нажать на ссылку Пока- зать все таблицы, после чего перетащить поля [Название института], [Название специальности] и [Название предмета] из справочных таблиц в соответствующие зоны отчета.

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

 

Рисунок 11.5 – Панель Группировка, сортировка и итоги для отчета Для каждого уровня группировки нажмем кнопку Больше ►, в по-

явившихся параметрах выберем с разделом примечания. Кроме того, зада- дим ненулевую высоту для зоны примечаний всего отчета.

Далее в Конструкторе отчета скопируем в зону примечаний для группы № зачетки поле [Оценка], в разделе Данные его Окна свойств из- меним выражение на =Avg([Оценка]) для вычисления среднего значения поля, зададим на вкладке Макет Окна свойств Формат поля Фиксирован- ный и Число десятичных знаков = 2, затем скопируем измененное поле в зоны примечаний других групп и всего отчета.

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

Разместим в отчете также надписи для итоговых значений, разлину- ем таблицу (при этом для линий нужно задать свойство Тип границы – Сплошная и ее цвет).

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

Вид отчета в Конструкторе после его модификации показан на ри- сунке 11.6.

Рисунок 11.6 – Отчет после модификации его в Конструкторе

Вид фрагментов отчета при его печати на бумаге показан на рисунке

11.7.

 

Рисунок 11.7 – Фрагменты отчета, напечатанного на принтере

2 Разработка запросов

Запросы при работе с базами данных в различных СУБД имеют близкий синтаксис, определяемый правилами языка SQL (Structured Query Language) и фактически являются текстовой командой на этом языке. Си- стема Access использует команды языка SQL не только для отбора данных (команда SELECT…), но и для изменения множества данных одной командой UPDATE…, добавления множества записей одной командой INSERT… и уда- ления группы записей одной командой DELETE …, а также для выполнения вычислений путем создания в запросах вычисляемых полей.

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

Принцип формирования запросов наиболее легко освоить при ис-

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

Для создания запроса на вкладке Создание нажимаем кнопку Мастер запросов, после чего открывается окно с названием Новый запрос, в кото- ром предлагается выбрать тип запроса, выберем первый тип – Простой за- прос, после чего откроется окно Мастера запросов, на первом шаге кото- рого требуется выбрать таблицы и поля для запроса (рисунок 11.8).

Рисунок 11.8 – Выбор полей на первом шаге Мастера запросов

Выбор полей может быть выполнен из нескольких таблиц базы. Для

нашего примера выбираем из таблицы Студенты все поля, кроме [Дата по- ступления], из таблицы Оценки 3 поля: [Семестр], [Оценка], [Дата получения] из таблицы Предметы поле [Название предмета].

На шаге 2 необходимо выбрать подробный или итоговый отчет, вы- бираем подробный.

На последнем шаге 3 задаем название запроса Математика и выбе- рем вариант Изменить макет запроса, после чего нажимаем кнопку Готово. Запрос открывается в Конструкторе запросов, его вид показан на рисунке 11.9.

Рисунок 11.9 – Конструктор запросов

При работе с Конструктором запросов используется контекстная вкладка Работа с запросами – Конструктор (см. рисунок 11.8).

В верхней половине конструктора запросов показаны выбранные в Мастере таблицы базы данных и связи между ними, в нижней части – таб- лица для выбора полей, задания сортировки и условий отбора данных.

Модифицируем запрос для задания условия отбора данных и упоря- дочения студентов по их фамилии. Для этого в колонке поля [Фамилия, имя, отчество] зададим сортировку по возрастанию, для поля [Семестр] за- дадим условие отбора 1 (первый семестр), для поля [Оценка] зададим усло- вие отбора 5 и для поля [Название предмета] зададим условие отбора Ма- тематика.

Сохраним запрос и посмотрим его текст в режиме SQL (Structured

Query Language). Текст запроса будет выглядеть следующим образом:

SELECT Студенты.[№ зачетки], Студенты.[Фамилия, имя, отчество], Сту- денты.[№ института], Студенты.[Код специальности], Студенты.Курс, Студен- ты.Группа, Оценки.Семестр, Оценки.Оценка, Оценки.[Дата получения], Пред- меты.[Название предмета]

FROM Студенты INNER JOIN (Предметы INNER JOIN Оценки ON Предме- ты.[№ предмета] = Оценки.[№ предмета]) ON Студенты.[№ зачетки] = Оцен- ки.[№ зачетки]

WHERE (((Оценки.Семестр)=1) AND ((Оценки.Оценка)="5") AND ((Пред- меты.[Название предмета])="Математика"))

ORDER BY Студенты.[Фамилия, имя, отчество];

Текст запроса состоит из нескольких частей:

1) после ключевого слова SELECT следует список полей, которые бу- дут представлены в результате выполнения запроса (например, Студен- ты.[№ зачетки] – имя таблицы и её поле, разделенные точкой);

2) после слова FROM следует список таблиц, из которых будут отби- раться данные, и условия объединения таблиц;

3) после слова WHERE следуют условия отбора данных;

4) после слов ORDER BY указаны условия упорядочения данных в за- просе.

Закроем окно конструктора и выполним запрос двойным щелчком мышью. Результат отбора данных будет показан на экране в виде таблицы. Результаты выполнения запроса (а также и данные таблиц) можно пред- ставить в виде диаграмм и графиков. Создадим запрос, в котором покажем в графическом виде средний балл по студенческим группам по предмету Математика. Как и ранее, выбор полей для запроса выполним в Мастере, далее в Конструкторе запросов на вкладке ленты Работа с запросами – Конструктор нажмем на кнопку Итоги, после чего в нижней части окна Конструктора запросов появится строка Групповая операция для задания условий группировки данных и создания итоговых вычисляемых полей (рисунок 11.10). Зададим групповую операцию AVG (расчет среднего зна- чения) для поля [Оценка] и условие отбора Математика для поля [Назва- ние предмета].

Получим следующий текст SQL-запроса:

SELECT Студенты.Группа, Avg(Оценки.Оценка) AS [Avg-Оценка]

FROM Студенты INNER JOIN (Предметы INNER JOIN Оценки ON Предме- ты.[№ предмета] = Оценки.[№ предмета]) ON Студенты.[№ зачетки] = Оцен- ки.[№ зачетки]

GROUP BY Студенты.Группа, Предметы.[Название предмета]

HAVING (((Предметы.[Название предмета])="Математика"));

Рисунок 11.10 – Запрос с группировкой данных в Конструкторе

Для представления данных запроса в виде графика выбираем Вид ► Сводная д иаграмма, после чего открывается окно Построителя диаграмм. Перетащим из окна Список полей диаграммы в поле для категорий (внизу диаграммы) поле запроса Группа, в поле для данных (вверху диаграммы) – поле Avg-Оценка. Получим Гистограмму, которую далее можно настроить в соответствии с правилами оформления. На рисунке 11.10 показана диа- грамма для приведенного выше запроса, после ее модификации с исполь- зованием окна свойств.

Рисунок 11.10 – Запрос с группировкой данных,

представленный в виде диаграммы

 

 

Учебное задание

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

2. Создать 2 запроса по примерам в этой лабораторной работе.

Лабораторная работа № 12.