Как можно сделать вычисления в запросах

Подсчет данных при помощи запроса

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

Выберите нужное действие

• Способы подсчета данных
• Подсчет данных с помощью строки итогов
• Подсчет данных с помощью итогового запроса
• Справочные сведения об агрегатных функциях

Способы подсчета данных

Подсчитать количество элементов в поле (столбце значений) можно с помощью функции Число. Функция Число принадлежит к ряду функций, называемых агрегатными. Агрегатные функции выполняют вычисления со столбцами данных и возвращают единственное значение. Кроме функции Число, в Access есть следующие агрегатные функции:

• Сумма для суммирования столбцов чисел;
• Среднее для вычисления среднего значения в столбце чисел;
• Максимум для нахождения наибольшего значения в поле;
• Минимум для нахождения наименьшего значения в поле;
• Стандартное отклонение для оценки разброса значений относительно среднего значения;
• Дисперсия для вычисления статистической дисперсии всех значений в столбце.

В Access предусмотрено два способа добавления функции Count и других агрегатных функций в запрос. Вы можете:

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

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

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

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

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

Общие сведения о типах данных см. в статье Изменение типа данных для поля.

Подсчет данных с помощью строки итогов

Чтобы добавить в запрос строку итогов, откройте его в режиме таблицы, добавьте строку, а затем выберите функцию Число или другую агрегатную функцию, например Сумма, Минимум, Максимум или Среднее. В этом разделе объясняется, как создать простой запрос на выборку и добавить строку итогов.

Создание простого запроса на выборку

1. На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
2. Дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе, а затем нажмите кнопку «Закрыть». Выбранные таблицы отображаются в виде окон в верхней части конструктора запросов. На рисунке показана типичная таблица в конструкторе запросов.
3. Дважды щелкните поля таблицы, которые вы хотите использовать в запросе. Вы можете включить поля, содержащие описательные данные, например имена и описания, но следует обязательно добавить поле, содержащее подсчитываемые значения. Каждое поле отображается в столбце в бланке запроса.
4. На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить. Результаты запроса отображаются в режиме таблицы.
5. При необходимости переключитесь в Конструктор и скорректируйте запрос. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Конструктор. После этого можно изменить запрос, добавив или удалив поля таблицы. Чтобы удалить поле, выберите столбец в бланке запроса и нажмите клавишу DELETE.
6. При необходимости вы можете сохранить запрос.

Добавление строки итогов

1. Откройте запрос в режиме таблицы. Если база данных имеет формат ACCDB, щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Режим таблицы. -или- Если используется база данных в формате MDB, созданная в более ранней версии Access, на вкладке Главная в группе Режимы щелкните стрелку под кнопкой Режим и выберите значение Режим таблицы. -или- Дважды щелкните запрос в области навигации. Запрос будет выполнен, а его результаты будут загружены в таблицу.
2. На вкладке Главная в группе Записи нажмите кнопку Итоги. Под последней строкой данных в таблице появится новая строка Итог.
3. В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.

Скрытие строки итогов

• На вкладке Главная в группе Записи нажмите кнопку Итоги.

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

Подсчет данных с помощью итогового запроса

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

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

Подсчет всех записей в запросе

1. На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
2. Дважды щелкните таблицу, которую вы хотите использовать в запросе, и нажмите кнопку «Закрыть». Таблица появится в окне в верхней части конструктора запросов.
3. Дважды щелкните поля, которые вы хотите использовать в запросе, и убедитесь, что включено поле, количество в которое нужно подсчитать. Можно подсчитать поля большинства типов данных, за исключением полей, содержащих сложные повторяющиеся скалярные данные, такие как поле многомерных списков.
4. На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги. В бланке появится строка Итог, а в строке для каждого поля запроса будет указано Группировка.
5. В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.
6. На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить. Результаты запроса отображаются в режиме таблицы.
7. При необходимости вы можете сохранить запрос.

Подсчет записей в группе или категории

1. На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
2. Дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе, а затем нажмите кнопку «Закрыть». Таблица (или таблицы) появится в окне в верхней части конструктора запросов.
3. Дважды щелкните поле, содержащее данные категории, а также поле, значения в котором вы хотите подсчитать. Запрос не может содержать других описательных полей.
4. На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги. В бланке появится строка Итог, а в строке для каждого поля запроса будет указано Группировка.
5. В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.
6. На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить. Результаты запроса отображаются в режиме таблицы.
7. При необходимости вы можете сохранить запрос.

Справочные сведения об агрегатных функциях

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

Поддерживаемые типы данных

Суммирует элементы в столбце. Подходит только для числовых и денежных данных.

«Число», «Действительное», «Денежный»

Вычисляет среднее значение для столбца. Столбец должен содержать числовые или денежные величины или значения даты или времени. Функция игнорирует пустые значения.

«Число», «Действительное», «Денежный», «Дата/время»

Подсчитывает число элементов в столбце.

Все типы данных, за исключением сложных повторяющихся скалярных данных, таких как столбец многозначных списков.

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

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

«Число», «Действительное», «Денежный», «Дата/время»

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

«Число», «Действительное», «Денежный», «Дата/время»

Стандартное отклонение

Показывает, насколько значения отклоняются от среднего.

Дополнительные сведения об этой функции см. в статье Отображение итогов по столбцу в таблице.

«Число», «Действительное», «Денежный»

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

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

«Число», «Действительное», «Денежный»

Пособие для студентов Модуль 3

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

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

Рис. 30. Запросы в окне базы данных.

Запросы лучше создавать вручную в режиме Конструктор. Для этого используется специальный значок Создание запроса в режиме конструктора в окне База данных. Он открывает специальный бланк, называемый бланком запроса по образцу (рис. 31). Он состоит из двух областей. В верхней отображается структура таблиц, к которым адресован запрос, а нижняя область разбита на столбцы – по одному столбцу на каждое поле будущей результирующей таблицы.

Рис. 31. Запрос в режиме Конструктор.

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

Упорядочение записей в результирующей таблице. Если необходимо, чтобы дан­ные, отобранные в результате работы запроса, были упорядочены по какому-либо полю, применяют сортировку. В нижней части бланка имеется специ­альная строка Сортировка. При щелчке на этой строке появляется раскры­вающийся список, в котором можно выбрать метод сортировки: по возрастанию или по убыванию. В результирующей таблице данные будут отсортированы по тому полю, для которого задан порядок сортировки. На рисунке 32 задана сортировка по полю Фамилия.

Управление отображением данных в результирующей таблице. В нижней части бланка запроса по образцу имеется строка Вывод на экран. По умолчанию предпо­лагается, что все поля, включенные в запрос, должны выводиться на экран, но это не всегда целесообразно. Бывают случаи, когда некое поле необходимо включить в запрос, но нежелательно, чтобы пользователь базы видел его содержание. В таких случаях отображение содержимого на экране запрещают сбросом флажка Вывод на экран. На рисунке 32 поле Город в результирующей таблице не будет показываться, хотя оно содержится в таблице. Необходимость включения этого поля в запрос обусловлена тем, что по нему задано условие отбора данных.

Рис. 32. Задание условий сортировки записей и показа полей в результирующей таблице запроса.

Сформировав структуру запроса, его закрывают, дают ему имя и в дальнейшем запускают двойным щелчком на значке в окне База данных.

Простые запросы на выборку

Такие запросы используются для отбора записей из одной или нескольких взаимосвязанных таблиц в соответствии с заданными критериями отбора. Критерии отбора записей записываются в виде условий отбора в строке Условие отбора бланка запроса по образцу и расположенных под ней строках. Условия отбора записываются по определенным правилам записи. В следующей таблице приведены примеры записи условий отбора.

Критерий отбора записей

Запись условия отбора

Значение в поле начинается с символа «А», остальные символы могут быть любыми

Значение в числовом поле > = 60

Как можно сделать вычисления в запросах

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

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

С другой стороны, при создании вычисляемого поля на основе содержимого текстовых полей, как правило, используется операция объединения текстовых значений, которая называется конкатенацией. В таблице 1 приведены операторы, которые используются в Access при построении выражений:

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

Рис. 1. Построитель выражений

С ее помощью можно выполнять различные вычисления с использованием данных, находящихся в БД — полей таблиц, запросов, форм и отчетов. Все перечисленные объекты БД находятся, соответственно, в папках Таблицы, Запросы, Forms и Reports в левой части Построителя.

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

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

Также существует возможность использования в выражении различных функций Access. Для этого в левой части окна необходимо открыть папку Функции и выбрать пункт Встроенные функции. Затем следует в средней части Построителя определить категорию, в которой находится требуемая функция, и выбрать после этого в правой части окна название искомой функций (рис. 2, 3). Создаваемое таким образом выражение отображается в верхней части окна Построителя выражений.

Рис. 2. Построитель выражений. Встроенные функции

Рис. 3. Построитель выражений. Текстовая функция Left

Например, необходимо в запросе ПреподавателиПредметы вместо поля Фамилия добавитъ вычисляемое поле, в котором содержались бы фамилия и инициалы преподавателя.

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

Примечание. Кнопка Построить позволяет не только создавать новые выражения, с ее помощью можно также изменять уже существующее выражение, которое в этом случае должно находиться в текущем поле.

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

Для создания текстовой строки с фамилией и инициалами необходимо несколько раз последовательно выполнить операцию конкатенации следующей структуры:

Фамилия & Символ Пробела & Инициал имени & Символ точки & Инициал отчества & Символ точки.

Все текстовые символы указываются в двойных кавычках, поэтому структура выражения примет такой вид:

Фамилия & » » & Инициал имени & «.» & Инициал отчества & «.».

Таким образом, начало выражения будет следующим:

Фамилия & » » &

Для работы с текстовыми значениями используются функции категории Текстовые. Чтобы получить инициал имени, необходимо воспользоваться одной из функций данной категории, а именно — функцией Lеft, которая возвращает первые n символов указанной строки.

Чтобы добавить функцию в выражение, необходимо дважды щелкнуть на папке Функции и выбрать после этого раздел Встроенные функции (рис. 2).

Затем во втором столбце необходимо выбрать требуемую категорию, в данном случае Текстовые, и после этого в третьем столбце выбрать нужную функцию, т.е. Left (рис. 3).

После того как функция выбрана, необходимо установить курсор в то место выражения, куда она должна быть вставлена, и нажать кнопку Вставить в окне Построителя выражений. В результате этих действий в выражение будет добавлен шаблон функции Left:

Left («stringexpr»; «n») ,

в котором необходимо указать значения требуемых параметров. В частности, вместо stringexpr нужно указать поле Имя, а вместо n — количество символов, которые необходимо получить из строки, т.е. 1. Таким образом, текущее выражение примет вид:

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

Рис. 4. Построитель выражений. Поле Имя таблицы Преподаватели

Таким образом, название поля с указанием имени таблицы необходимо отображать в следующем формате:

[Имя таблщы] ! [Название поля]

После указания первого инициала необходимо поставить точку, т.е. указать в выражении строку «.». Затем аналогичным способом добавить инициал отчества и снова поставить точку. Таким образом, будет получено следующее выражение:

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

Названия полей запроса формируются автоматически. Для полей таблиц названия совпадают с названиями соответствующих полей запроса (например, поле Название). С другой стороны, названия вычисляемых полей формируются автоматически; Выражение1, Выражение2 и т. д. Очевидно, что такие названия не являются информативными.

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

Рис. 5. Диалоговое окно Свойства поля

Таким образом, был создан и выполнен запрос на выборку с использованием вычисляемого поля (рис. 6).

Рис. 6. Запрос на выборку с вычисляемым полем ФИО

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

На следующем шаге вы узнаете о групповых операциях, которые можно использовать при создании запроса.

Вычисляемые поля в запросах

Запрос можно использовать для выполнения расчетов и подведения итогов из исходных таблиц.

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

Вычисляемое поле может также содержать результат конкатенации (объединения) значений текстовых полей. Для этого заключите текст в кавычки, в качестве оператора конкатенации используется символ «&». Например, можно создать поле, которое будет содержать результат объединения поля [Фамилия] и поля [Имя].

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

Создание вычисляемого поля осуществляется путем простого ввода выражения для вычисления в ячейку Поле пустого столбца бланка запроса.

Пример 2.11. Требуется рассчитать стоимость товара со скидкой. Решение