Аналитическая выборка данных из БД неразрывно связана с группировкой записей и использованием агрегатных функций, применяемых к сгруппированным записям.
Вычисления такого рода, как и вычисляемые поля, рассмотренные выше, не хранятся в базовой таблице или таблицах, а производятся всякий раз, когда выполняется запрос, тем самым позволяют всегда представить текущее содержимое базы данных.
Аналитическая выборка данных позволяет производить такие вычисления в запросе, с помощью которых по группам записей или по всем записям можно найти сумму, среднее арифметическое, число значений, минимальное или максимальное значение, стандартное отклонение или дисперсию, используя соответствующую агрегатную функцию:
Sum Сумма значений поля.
Avg Среднее арифметическое значений поля.
Min Наименьшее значение поля.
Max Наибольшее значение поля.
Count Число значений поля без учета пустых (Null) значений.
StDev Среднеквадратичное отклонение от среднего значения поля.
Var Дисперсия значений поля.
Задание 23. Создайте запрос КоличествоСтудентовВПодгруппах, определяющий количество студентов в каждой подгруппе.
• Для решения поставленной задачи создайте простой запрос, содержащий поля Подгруппа и Фамилия из таблицы Студенты.
• Далее необходимо в запрос добавить групповую операцию. Применение к полученному набору записей групповых операций приводит к тому, что по каждой группе одинаковых значений выводится только одна запись с результатом вычислений соответствующей агрегатной функции.
Для применения групповых операций необходимо в режиме конструктора кнопкой на панели инструментов вызвать новую строку – Групповая операция (рис. 16), которая появляется в табличной части окна конструктора запросов и содержит список команд группировки и агрегатных функций, где:
Группировка определяет группы, для которых выполняются вычисления.
Конкретная агрегатная функция (перечислены выше) создает вычисляемое поле, в котором размещается результат агрегирования соответствующего поля группы записей.
Условие указывается, если поле используется для проведения фильтрации до проведения групповых операций и вычислений. Это поле не будет участвовать в группировке и не выводится на экран.
• Для того чтобы переименовать столбец с примененной агрегатной функцией, необходимо в режиме конструктора в строке Поле соответствующего столбца ввести выражение ИмяСтолбца:ИмяПоля (рис. 16).
Задание 24. Вычислите средний стаж работы преподавателей по кафедрам. Сохраните запрос под именем СтажПреподавателей.
7.4. Создание перекрестных запросов в режиме Конструктора
Ранее мы уже рассматривали создание перекрестных запросов с помощью мастера. Существенным недостатком использования мастера является невозможность включения данных из нескольких таблиц и необходимость создания вспомогательных запросов. Режим конструктора позволяет устранить этот недостаток.
В режиме конструктора необходимо создать сначала простой запрос на выборку (можно использовать данные из нескольких таблиц), а затем преобразовать его в перекрестный командой Перекрестный в меню Запрос.
При этом в табличной части окна (рис. 17) появляются строки Групповая операция и Перекрестная таблица, которые являются отличительными чертами запросов такого вида. Строка Перекрестная таблица предусматривает следующие значения:
Заголовки строк/столбцов – поля, значения которых группируются по строкам и столбцам соответственно и являются их заголовками.
Значение – поле, подвергающееся анализу, т. е. к значениям которого применяется указанная в групповой операции агрегатная функция.
(не отображается) – поля, которые применяются для установки условий.
Задание 25. Создайте перекрестный запрос СоставПодгрупп, отражающий количество студентов мужского и женского пола в каждой подгруппе.
• Создайте простой запрос, включив в него поля Фамилия, Пол, Подгруппа из таблицы Студенты.
• Преобразуйте его в перекрестный.
• В строке Перекрестная таблица установите значение Заголовки строк для поля Подгруппа и Заголовки столбцов для поля Пол.
• Определите групповую операцию Count для поля Фамилия.
• Сохраните запрос.
Задание 26. Создайте перекрестный запрос МесяцРождения, иллюстрирующий распределение количества студентов по месяцу рождения на каждом курсе.
7.5. Создание запросов на изменение в режиме конструктора
Запросом на изменение называют запрос, который за одну операцию вносит изменения в несколько записей таблицы. Существует четыре типа запросов на изменение:
• на создание таблицы,
• на удаление записей,
• на обновление записей,
• на добавление записей.
Запрос на создание таблицы создает новую таблицу на основе всех или части данных из одной или нескольких таблиц. Такой запрос полезен для выполнения следующих действий: создание таблицы для экспорта в другую базу данных MS Access; создание резервной копии таблицы; создание архивной таблицы, содержащей старые записи.
Запрос на удаление удаляет группу записей из одной или нескольких таблиц. С помощью такого запроса можно удалять только всю запись, а не значения отдельных полей внутри нее.
При помощи запроса на удаление можно удалить записи из одной таблицы, из нескольких таблиц с отношением «один-к-одному» или из нескольких таблиц с отношением «один-ко-многим», если разрешено каскадное удаление.
При выполнении запроса на удаление записей необходимо учитывать следующие факторы:
• Отменить операцию удаления невозможно. Поэтому, прежде чем выполнить такой запрос, необходимо просмотреть выбранные для удаления данные.
• Рекомендуется всегда делать резервные копии данных. Таким образом, если были удалены не те записи, их можно восстановить из резервных копий.
• Иногда при выполнении запроса на удаление также могут быть удалены и некоторые записи из связанных таблиц, не включенных в запрос. Это случается, если в запрос включена только таблица, находящаяся на стороне «один» отношения «один-ко-многим», и для этого отношения было установлено каскадное удаление. При удалении записей из данной таблицы будут также удаляться записи из таблицы, находящейся на стороне «многие».
Запрос на обновление вносит общие изменения в группу записей одной или нескольких таблиц и позволяет изменять данные в существующих таблицах.
Запрос на добавление добавляет группу записей из одной или нескольких таблиц в конец одной или нескольких таблиц.
Общие шаги для создания запроса на изменение:
1-й шаг – создать запрос на выборку;
2-й шаг – задать все требуемые условия отбора;
3-й шаг – изменить тип запроса, выбрав нужный в меню Запрос;
4-й шаг – в режиме Таблица просмотреть записи, которые подвергнутся действию данного запроса (изменения при этом не происходят и не отображаются);
5-й шаг – выполнить запрос командой Запрос/Запуск или одноименной кнопкой ;
6-й шаг – при необходимости сохранить и закрыть запрос.
Задание 27. С помощью запроса на создание таблицы создайте таблицу Выпускники, содержащую всю информацию о студентах пятого курса.
Вызовите конструктор запросов и в качестве источника данных выберите таблицы Студенты, Подгруппы, Успеваемость.
Преобразуйте запрос в запрос на создание (меню Запрос/Создание таблицы).
Задайте имя таблицы – Выпускники.
Включите в запрос все поля из этих таблиц, используя знак «*» после имени таблицы.
Задайте условие отбора (курс = 5).
Просмотрите отобранные записи командой Вид/Таблица или кнопкой (при этом создания таблицы не происходит).
Для создания таблицы выполните запрос (командой Запрос/Запуск или кнопкой ).
Примечание. При каждом следующем запуске запроса таблица Выпускники будет удаляться и создаваться заново.
Задание 28. Создайте запрос УдалениеВыпускников, удаляющий всю информацию о студентах 5-го курса.
При удалении студента из базы, во избежание нарушения целостности данных, необходимо удалить всю информацию о данном студенте из всех таблиц. Так, если студент является старостой, то номер его зачетной книжки хранится в поле Староста таблицы Подгруппы и поэтому необходимо заранее проверить и исправить значение этого поля (например, оставить пустым). Кроме того, для студента в таблице Успеваемость содержится информация о сданных им зачетах и экзаменах и при удалении студента соответствующие записи также должны быть удалены. Поэтому необходимо разрешить Каскадное удаление связанных полей для соответствующей связи в окне Схема данных.
Вызовите конструктор запросов и в качестве источника данных выберите таблицы Студенты и Подгруппы.
Включите в запрос все поля из таблицы Студенты и поле Курс из таблицы Подгруппы.
Задайте условие для отбора студентов пятого курса.
Преобразуйте запрос в запрос на удаление, при этом в табличной части окна запросов появляется строка Удаление. Значение «Из» в данной строке определяет таблицу, из которой производится удаление записей. Значение «Условие» устанавливается для полей, которые задают условия отбора удаляемых записей.
Просмотрите записи в режиме таблицы, после чего выполните запрос.
Сохраните запрос под именем УдалениеВыпускников.
8. Отчёты
Отчет – это зафиксированный на бумаге результат анализа и обработки данных, поэтому он не используются как средство ввода или редактирования данных. Отчеты Access – это оптимальное средство для подготовки данных к печати. Конечно, печать данных можно осуществить непосредственно из таблицы или формы, но лишь при помощи отчетов можно представить и структурировать данные, увидеть их в том виде, в котором они будут выведены на лист бумаги посредством принтера.
Как и другие объекты Access, отчеты можно создать при помощи автоотчетов, мастеров или конструктора
Так хочется купить компьютерную игру, чтобы не оказаться обманутым! В Игромагазе вы можете быть уверены в надежности доставки
