Как сделать итоговый запрос в access
Добавил пользователь Евгений Кузнецов Обновлено: 04.10.2024
Итоговый запрос – это запрос, в котором выводятся результаты статистических расчетов по какой-либо группе записей из одной или нескольких таблиц. Можно находить сумму (функция Sum), среднее значение (функция Avg), наибольшее значение (функция Max) или наименьшее значение (функция Min), количество знаний в группе (функция Count).
Процедура создания итогового запроса похожа на процедуру создания запроса на выборку. При выполнении такого запроса требуется группировать записи по совпадающим значениям в каком-либо поле таблицы. Для выполнения
Например, можно создать запрос для определения средней, наибольшей и наименьшей оценки для каждого студента, как показано на рисунке 11.
Рисунок 11 − Результат выполнения итогового запроса
Рисунок 12 − Создание итогового запроса
4.4 Перекрестные запросы
Перекрестные запросы используют для расчетов и представления данных в структуре, облегчающей их анализ. Перекрестный запрос подсчитывает сумму, среднее, число значений или выполняет другие статистические расчеты, после чего результаты группируются в виде таблицы по двум наборам данных, один из которых определяет заголовки столбцов, а другой заголовки строк.
Для расчета итоговых вычислений используют стандартные итоговые функции, например:
• Sum − сумма значений некоторого поля для группы;
• Avg − среднее от всех значений поля в группе;
• Max, Min − максимальное, минимальное значение поля в группе;
• Count − число значений поля в группе без учета пустых значений.
Создание перекрестного запроса с помощью мастера
2 Выбрать таблицу или запрос, поля которых будут использоваться в перекрестном запросе. Нажать кнопку Далее .
3 Выбрать поля, значения которых будут использованы в качестве заголовков строк. Нажать кнопку Далее .
4 Выбрать поля, значения которых будут использованы в качестве заголовков столбцов. Нажать кнопку Далее .
5 Выбрать поле, значения которого будут выводиться на пересечении строк и столбцов. Выбрать среди приведенных функций вид вычисления.
6 Если не требуется вычислять и выводить итоговое значение для каждой строки запроса, то следует снять флажок с соответствующей опции. Нажать кнопку Далее .
7 Ввести имя для запроса и нажать кнопку Готово .
Если получился не тот запрос, который был нужен, можно снова создать запрос с помощью мастера или изменить этот запрос в режиме конструктора.
Создание перекрестного запроса с помощью конструктора запросов
3 Дважды щелкнуть объекты, которые нужно добавить в запрос, а затем нажать кнопку Закрыть .
5 а) В среде Microsoft Access 2003: на панели инструментов нажать кнопку Тип
запроса и выбрать Перекрестный ;
9 Выполните одно из следующих действий:
• добавить поле, для которого необходимо установить условие отбора, в бланк запроса;
10 а) В среде Microsoft Access 2003: чтобы просмотреть результаты запроса, нажать кнопку Запуск на панели инструментов или выбрать команду Запрос / Запуск , или переключиться в режим таблицы с помощью кнопки Вид на панели инструментов;
Результат выполнения перекрестного запроса представлен на рисунке 14.
Рисунок 14 – Результат выполнения перекрестного запроса
Программа Access от компании Microsoft предоставляет пользователям широкие возможности для создания и работы с базами данных. В наше время базы данных очень широко используются во всевозможных сферах и областях. В целом, можно сказать, что сейчас без них не обойтись. В связи с этим, очень важно уметь работать с таблицами Access. Основным элементом взаимодействия пользователя с базой данных является запрос. В этой статье подробно рассмотрим, как создать запрос в Access. Давайте же начнём. Поехали!
Прежде всего, разберёмся с тем, что такое запрос. Это элемент базы данных, который служит для выбора нужной информации из таблицы. Они бывают двух видов:
- На выборку (позволяют получать данные и выполнять операции над ними);
- На изменение (позволяют добавлять, удалять и изменять значения).
Указав условие для отбора данных, мы получаем нужную выборку
.jpg)
С помощью запросов в базах данных выполняют такие операции, как отбор данных, их сортировка и фильтрация. Запрос можно использовать для выполнения расчетов, объединения данных из разных таблиц, а также для добавления, изменения или удаления данных в таблице. Это очень гибкий инструмент, и существует много типов запросов, а выбор типа определяется назначением запроса.
Запрос — объект БД, который используется для реализации эффективного поиска и обработки данных.
Источником данных для запросов могут быть не только одна или несколько таблиц, но другие запросы. Запросы не хранят данные, а только отображают данные источников. На основе запросов могут быть построены формы и отчеты.
Самый распространенный вид запросов — запрос на выборку. Он предназначен для отбора данных из источника в соответствии с некоторым условием. Условие запроса — это выражение, которое СУБД сравнивает со значениями в полях запроса, чтобы определить, следует ли включать в результат записи, содержащие то или иное значение.
Запрос на выборку позволяет:
1. Просматривать значения только из полей, которые вас интересуют.
2. Просматривать записи, которые отвечают указанным вами условиям.
3. Использовать выражения в качестве полей.
По особенностям действия среди запросов на выборку можно выделить простые запросы, групповые запросы и запросы с вычисляемым полем.
Основные режимы работы с запросами в Access:
1. Режим таблицы. Отображает информацию запроса на выборку в режиме таблицы.
2. Конструктор. В этом режиме определяется структура запроса и условия выбора данных (см. Приложение к главе 1).
Создать запрос можно с помощью Мастера запросов либо в Конструкторе (пример 5.2).
Мастер запросов позволяет автоматически создавать запросы на выборку. Однако при использовании мастера не всегда можно контролировать процесс создания запроса, но таким способом запрос создается быстрее. Необходимо просто выполнить последовательность действий, предлагаемых мастером на каждом этапе (пример 5.3).
Основные этапы создания запроса на выборку:
1. Выбор инструмента создания запроса.
2. Определение вида запроса.
3. Выбор источника(ов) данных.
4. Добавление из источника(ов) данных полей, которые должен содержать запрос.
5. Определение условий, которые формируют набор записей в запросе.
6. Добавление группировки, сортировки и вычислений (может отсутствовать).
Действие простых запросов на выборку ограничивается отбором данных по некоторым условиям без их обработки.
Примеры записи условий в запросах:
Действие в запросе
Поля с числовым типом данных
Выбираются записи, у которых значение в этом поле больше 0 и меньше 8.
Выбираются записи, у которых значение в этом поле не равно 0.
Поля с текстовым типом данных
Если значение в поле записи равно Орша, то запись включается в результат запроса.
В результат запроса включаются записи, у которых значение соответствующего поля заканчивается на букву к. После выполнения запроса условие будет дополнено оператором Like, который позволяет использовать символы шаблона.
Если необходимо найти несколько значений полей, можно использовать оператор In . Этот оператор позволяет выполнить проверку на равенство любому значению из списка, который задается в круглых скобках.
Если требуется использование нескольких условий для разных полей, необходимо учитывать, что между условиями, записанными в одной строке, выполняется логическая операция And , а между условиями, записанными в разных строках, выполняется логическая операция Or .
В режиме конструктора процесс создания запроса находится под вашим контролем, однако здесь есть вероятность допустить ошибку и необходимо больше времени, чем в мастере (пример 5.4).
После создания запроса на выборку его необходимо запустить, чтобы посмотреть результаты, т. е. открыть в режиме таблицы. Сохранив запрос, его можно использовать в качестве источника данных для формы, отчета или другого запроса.
Все запросы, которые рассмотрены в примерах 5.3 и 5.4 , содержат конкретные значения названий, имен, времени и т. д. Если требуется повторить такой запрос с другими значениями в условиях отбора, его нужно будет открыть в конструкторе, изменить условие и выполнить. Чтобы не делать многократно этих операций, можно создать запрос с параметрами. При выполнении такого запроса выдается диалоговое окно Введите значение параметра, в котором пользователь может ввести конкретное значение, а затем получить нужный результат. Параметр запроса определяется в строке Условие отбора конструктора для столбца, содержащего запрашиваемые значения. Параметром является слово или фраза, заключенные в квадратные скобки. Параметр будет выдаваться в виде приглашения в диалоговом окне при выполнении запроса (пример 5.5).
В запросах так же, как и в отчетах, можно создавать вычисляемые поля. Для решения ряда вопросов работы с данными используется запрос итоговых значений. Такой запрос представляет собой запрос на выборку, позволяющий группировать данные и производить вычисления.
В запросе итоговых значений в вычисляемом поле используют статистические функции. Задать статистическую функцию для вычисления итоговых значений по каждому из полей запроса можно путем выбора ее из раскрывающегося списка, который появляется после установки курсора в строке Групповая операция (пример 5.6).
Наряду с запросами на выборку часто применяются запросы на действие. С помощью таких запросов можно обновлять значения полей записей, добавлять новые или удалять уже существующие записи. В СУБД Access такие запросы можно создать в режиме конструктора, воспользовавшись инструментами группы Тип запроса:
Пример 5.1. Режимы работы с запросами.
Режим SQL позволяет создавать и просматривать запросы с помощью инструкций языка SQL.
SQL (англ. structured query language — язык структурированных запросов). Применяется для создания, редактирования и управления данными в реляционной базе данных.
Пример 5.2. Группа инструментов Запросы вкладки Создание.
Пример 5.3. Создание запроса на выборку с помощью Мастера запросов.
1. Выбрать инструмент .
2. Выбрать вид запроса.
3. Выбрать источник данных.
4. Задать поле, содержащее повторяющееся значение.
5. Выбрать поля для отображения вместе с повторяющимися значениями.
6. Просмотреть и/или сохранить запрос.
Пример 5.4. Создание простых запросов на выборку с помощью Конструктора запросов.
1. Выбрать инструмент
2. Выбрать источник данных.
3. Добавить поля таблицы, которые будет содержать запрос. Для этого выполнить двойной щелчок по каждому из названий полей в макете таблицы.
4. Записать условие формирования набора записей в запросе.
4.1. Выбор по полю с текстовым типом данных.
4.2. Выбор по полю с числовым типом данных.
4.3. Использование составного условия.
5. Сохранить запросы.
Пример 5.5. Создание запроса с параметрами.
1. Открыть один из запросов, созданных в примере 5.4 в конструкторе.
2. Изменить условия отбора на:
3. Сохранить с новым именем и открыть в режиме таблицы.
4. В диалоговом окне набрать одно из названий кинотеатра.
5. Просмотреть запрос.
Пример 5.6. Создание итогового запроса.
Создать итоговый запрос, определяющий, сколько мальчиков и сколько девочек посещают факультатив по математике.
4. Добавить вычисляемое поле (в строке нового поля Групповая операция в списке выбрать функцию Count).
Если полностью заполнить данными таблицу Комплектующие, введя параметры всех компонентов, входящих в сборочную спецификацию персонального компьютера, то можно узнать, во что обходится себестоимость комплектующих узлов. Запросы, выполняющие вычисления по всем записям для какого-либо числового поля, называются итоговыми запросами. В итоговом запросе может рассчитываться сумма значений или величина среднего значения по всем ячейкам поля, может выбираться максимальное или минимальное значение данных в поле, может также исполняться иная итоговая функция. Итоговые запросы, как и запросы на выборку, готовятся с помощью бланка запроса по образцу.
Цены на эти изделия для каждого класса проставьте произвольно. Прочие поля таблицы можно не заполнять — в формировании итогового запроса они участвовать не будут.
Закройте таблицу Комплектующие.
Откройте панель Запросы щелчком на одноименной кнопке окна Комплектующие: база данных. Выполните двойной щелчок на значке Создание запроса в режиме конструктора. В открывшемся диалоговом окне Добавление таблицы выберите таблицу Комплектующие, на основе которой будет разрабатываться итоговый запрос. Закройте окно Добавление таблицы. В бланк запроса по образцу введите следующие поля таблицы Комплектующие: Класс, Компонент, Цена.
Для поля Класс включите сортировку по возрастанию. Включите также сортировку по полю Цена, но на этот раз — по убыванию.
На панели инструментов Microsoft Access щелкните на кнопке Групповые операции или воспользуйтесь строкой меню (Вид ► Групповые операции). Эта команда необходима для создания в нижней части бланка строки Групповые операции. Именно на ее базе и создаются итоговые вычисления. Все поля, отобранные для запроса, получают в этой строке значение Группировка.
Для поля, по которому производится группировка записей (в нашем случае — Класс), оставьте в строке Групповые операции значение Группировка. Для остальных полей щелкните в этой строке — появится кнопка раскрывающегося списка, из которого можно выбрать итоговую функцию для расчета значений в данном поле.
Для поля Цена выберите итоговую функцию Sum для определения стоимости изделия как суммы стоимостей комплектующих. Для поля Компонент выберите итоговую функцию Count, определяющую общее количество записей, вошедших в группу. В нашем случае это количество узлов, из которых собран компьютер.
Закройте бланк запроса по образцу и дайте ему имя: Расчет стоимости изделия. Запустите запрос и убедитесь, что он правильно работает. Закройте все объекты базы данных. Завершите работу с программой Microsoft Access.
Мы научились создавать так называемые итоговые запросы. Это запросы, производящие вычисления по всем значениям одного поля. Мы научились также выбирать используемую итоговую функцию.
Читайте также: