Использование запросов в отчетах

Запросы в Access

Конспект лекций для лабораторной работы №3

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

В сущности, запрос представляет собой вопрос, сформулированный в терминах базы данных, например: «Какие записи в таблице Клиенты содержат значение 98052 в поле ПочтовыйИндекс?». При выполнении запроса Access находит в указанных таблицах данные, соответствующие заданному условию, и отображает их в табличном виде.

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

Вверху окна запроса располагаются четыре окошка, содержащие списки полей таблиц, включенных в запрос. Линии, соединяющие общие поля, обозначают связи между таблицами. Первая строка бланка запроса содержит имена полей, включенных в запрос, а во второй строке указана таблица, к которой принадлежит каждое поле. Третья строка Групповая операция (Total) позволяет выполнять вычисления над значениями полей, а четвертая указывает принцип сортировки записей. Флажок в пятой строке Вывод на экран (Show) определяет, будет ли поле отображаться в результатах запроса. Шестая строка с именем Условие отбора (Criteria) позволяет задать критерий отбора записей из таблицы, а седьмая строка задает альтернативный критерий.

На первый взгляд все это кажется довольно сложным. Однако когда вы приступите к построению собственного запроса, исходя из логических соображений, все вышесказанное начнет приобретать смысл. Тем более что от вас не потребуется особых усилий. Функция Мастер запросов (Query Wizard) проведет вас через все необходимые шаги по созданию запроса и сохранит его в качестве объекта базы данных для дальнейшего использования.

 

Ранее отмечалось, что для работы с данными, отобранными в соответствии с каким-либо условием, может быть использована возможность установить фильтр для таблицы базы данных или формы (в пункте меню Записи). В том же пункте меню есть раздел Расширенный фильтр, который открывает окно Конструктора запросов. Кроме того, Конструктор запросов фактически уже использовался при описании источника записей для отчета (см. рисунок 6 к лабораторной работе №2).

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

Запросы в системе Access бывают нескольких видов:

1. Запрос для отбора данных по заданным сложным условиям из одной или нескольких таблиц баз данных, с группировкой данных для расчета итогов, с показом результатов выполнения запроса в виде таблицы, либо с использованием его для форм и отчетов; после редактирования данных в таблице запроса данные таблиц базы могут обновляться (с некоторыми ограничениями).

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

3. Запрос на создание новой таблицы.

4. Запросы на изменение данных:

· обновление данных - команда занесения общих изменений в группу записей одной или нескольких таблиц;

· добавление данных - команда добавления группы записей из одной или нескольких таблиц в конец одной или нескольких таблиц;

· удаление данных - команда удаления группы записей из одной или нескольких таблиц.

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

На первом шаге следует выбрать таблицы и поля, которые нужно включить в запрос. Для нашего примера выбираем из таблицы SPISOK все поля, кроме DATA_P и N_PASP, из таблицы OCENKI - первые 4 поля и 2 поля таблицы PREDM (см. рисунок 1).

 

Рисунок 1 — Выбор полей в Мастере запросов

 

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

 

Рисунок 2 — Конструктор запросов

 

В верхней части Конструктора запросов показаны таблицы, используемые для отбора данных и связи между ними, в нижней части - таблица для выбора полей, группировки данных (если строки "Групповые операции" нет, нужно выбрать эту команду в главном меню Microsoft Access в пункте "Вид"), задания сортировки и условий отбора.

Модифицируем запрос для задания условия отбора данных и упорядочения студентов по их фамилии. Для этого в колонке поля FIO зададим сортировку по возрастанию, для поля SEMESTR зададим условие отбора 1 (первый семестр), для поля BALL зададим условие 5 и для поля PREDMET зададим условие "математика". Если в условии отбора написать текст в квадратных скобках, при выполнении запроса появится окно для ввода этого параметра. Например, если для поля PREDMET в условии написать [Задайте предмет], можно будет использовать один и тот же запрос для отбора данных по разным предметам.

Можно также убрать галочки у тех полей, которые вы не хотите показывать на экране.

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

 

SELECT Spisok.NZ AS Spisok_NZ, Spisok.FIO, Spisok.N_FCLT, Spisok.N_SPECT,

Spisok.KURS, Spisok.N_GRUP, OCENKI.SEMESTR, OCENKI.N_PREDM AS OCENKI_N_PREDM,

OCENKI.BALL, PREDMETS.N_PREDM AS PREDMETS_N_PREDM, PREDMETS.NAME_P

FROM Spisok INNER JOIN (PREDMETS INNER JOIN OCENKI

ON PREDMETS.N_PREDM = OCENKI.N_PREDM) ON Spisok.NZ = OCENKI.NZ

WHERE (((OCENKI.SEMESTR)=1) AND ((OCENKI.BALL)="5") AND ((PREDMETS.NAME_P)="математика"))

ORDER BY Spisok.FIO;

 

Закроем окно конструктора и выполним запрос командой Открыть или двойным щелчком мышью. Результат отбора данных будет показан на экране в виде таблицы (см. рисунок 3). Следует помнить, что редактирование данных этой таблицы приведет к изменению информации в таблицах базы данных!!!!

 

Рисунок 3 — Результаты выполнения запроса

 

Результаты выполнения запроса или данные таблиц можно представить в виде диаграмм и графиков. Создадим запрос, в котором покажем в графическом виде средний балл по студенческим группам по предмету "Математика" (№ предмета = 1). Для группировки данных, как отмечалось выше, в пункте "Вид" меню системы ставим галочку у строки "Групповые операции".

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

 

SELECT Spisok.N_GRUP, OCENKI.N_PREDM, Avg(OCENKI.BALL) AS [Avg-BALL]

FROM Spisok INNER JOIN OCENKI ON Spisok.NZ = OCENKI.NZ

GROUP BY Spisok.N_GRUP, OCENKI.N_PREDM

HAVING (((OCENKI.N_PREDM)=1));

 

Для представления данных в виде графика в меню Вид выбираем пункт Сводная диаграмма, после чего открывается окно Построителя диаграмм. Методы оформления диаграмм аналогичны использованию объекта Диаграмма Microsoft Graph в программах Microsoft Word или Excel. На рисунке 4 показана диаграмма для приведенного выше запроса. На рисунке 5 приведена трехмерная диаграмма для запроса следующего вида:

 

SELECT DISTINCTROW FCLT.NAME_F, PREDMETS.NAME_P, Avg(OCENKI.BALL) AS [Avg-BALL]

FROM PREDMETS INNER JOIN ((Spisok INNER JOIN OCENKI ON Spisok.NZ = OCENKI.NZ) INNER

JOIN FCLT ON Spisok.N_FCLT = FCLT.N_FCLT) ON PREDMETS.N_PREDM = OCENKI.N_PREDM

GROUP BY FCLT.NAME_F, PREDMETS.NAME_P;

 

Рисунок 4 — Результаты выполнения запроса с группировкой данных, представленные в виде диаграммы

 

Рисунок 5 — Результаты выполнения запроса с группировкой данных, представленные в виде трехмерной диаграммы

 

С использованием запросов других видов одной командой можно изменять (команда SQL UPDATE), либо удалять (команда SQL DELETE) данные множества записей таблицы, отобранных по какому-либо условию, а также добавлять записи из других таблиц (команда SQL INSERT).