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

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

Задача 1. В таблице ТОВАР имеются поля ЦЕНА и СТАВКА_НДС, вычислите цену с учетом НДС и сравните ее с полученной в вычисляемом поле таблицы Цена с НДС.

1. Создайте в режиме конструктора запрос на выборку для таблицы ТОВАР. Перетащите в бланк запроса поля НАИМ_ТОВ, ЦЕНА, СТАВКА_НДС и Цена с НДС (рис. 4.6).
2. Для подсчета цены с учетом НДС создайте вычисляемое поле, записав в пустой ячейке строки Поле (Field) выражение [ЦЕНА]+[ЦЕНА]*[СТАВКА_НДС].
3. Для отбора записей со значением выше 5000 в вычисляемом поле в строку Условие отбора (Criteria) введите > 5000
4. После ввода выражения система по умолчанию формирует имя вычисляемого поля Выражение 1, которое становится заголовком столбца в таблице с результатами выполнения запроса. Это имя вставится перед выражением [ЦЕНА]+[ЦЕНА]*[СТАВКА_НДС]. Для каждого нового вычисляемого поля в запросе номер выражения увеличивается на единицу. Имя вычисляемого поля отделяется от выражения двоеточием. Для изменения имени установите курсор мыши в вычисляемом поле бланка запроса и нажмите правую кнопку мыши. В контекстно-зависимом меню выберите Свойства (Properties) поля и в строку Подпись (Caption) введите новое имя поля ― Цена с НДС1 . Теперь в таблице с результатами выполнения запроса в заголовке вычисляемого столбца отобразится это имя. Имя поля может быть исправлено также непосредственно в бланке запроса.
5. Для отображения результата выполнения запроса щелкните на кнопке Выполнить (Run) в группе Результаты (Results). Вычисляемое поле таблицы и за-проса имеют одинаковые значения.
6. Измените в одной из записей запроса цену товара. Значения в обоих вычисляемых полях будут моментально пересчитаны.
7. Для формирования сложного выражения в вычисляемом поле или условии отбора целесообразно использовать построитель выражений. Построитель позволяет выбрать необходимые в выражении имена полей из таблиц, запросов, знаки операций, функции. Удалите выражение в вычисляемом поле и используйте построитель для его формирования.
8. Вызовите построитель выражений (Expression Builder), нажав кнопку Построитель (Builder) в группе Настройка запроса (Query Setup) ленты Конструктор (Design), или выбрав Построить (Build) в контекстно-зависимом меню. Курсор мыши должен быть установлен предварительно в ячейке ввода выражения.
9. В левой части окна Построитель выражений (Expression Builder) (рис. 4.7) выберите таблицу ТОВАР, на которой построен запрос. Справа отобразится список ее полей. Последовательно выбирайте нужные поля и операторы, двойным щелчком вставляя в выражение. Выражение сформируется в верхней части окна. Обратите внимание, построитель перед именем поля указал имя таблицы, которой оно принадлежит, и отделил его от имени поля восклицательным знаком.
10. Завершите процесс построения выражения в вычисляемом поле, щелкнув на кнопке ОК.
11. Сохраните запрос под именем ― Цена с НДС и закройте его.
12. Выполните сохраненный , выделив его в области навигации и выбрав в контекстном меню команду Открыть (Open).

Задача 2. В вычисляемых полях и условиях отбора можно использовать встроенные функции. В Access определено более 150 функций.
Пусть необходимо выбрать все накладные, по которым производилась отгрузка в заданном месяце. В НАКЛАДНАЯ дата отгрузки хранится в поле ДАТА_ОТГ с типом данных Дата/время (Date/Time).

1. Создайте в режиме конструктора запрос на выборку для таблицы НАКЛАДНАЯ. Перетащите в бланк поля НОМ_НАКЛ и КОД_СК (рис. 4.8).
2. Создайте вычисляемое поле в пустой ячейке строки Поле (Field), записав туда одно из выражений: Format([НАКЛАДНАЯ]![ДАТА_ОТГР];»mmmm») ― эта функция возвратит пол-ное название месяца
   или Format([НАКЛАДНАЯ]![ДАТА_ОТГР];»mm») ― эта функция возвратит номер месяца.
3. Для отбора накладных, выписанных в заданном месяце, в вычисляемом поле в строку Условие отбора (Criteria) введите название месяца, например март (рис. 4.8), или номер месяца, например 3 в соответствии с параметром в функции Format.
4. Выполните запрос, нажав кнопку Выполнить (Run) в группе Результаты (Results) на вкладке ленты Работа с запросами | Конструктор (Query Tools | Design).
5. Запишите в вычисляемом поле функцию Month(НАКЛАДНАЯ!ДАТА_ОТГ), и убедитесь, что эта функция возвращает выделенный из даты номер месяца.
6. Для выборки всех строк, относящихся ко второму кварталу, в строку Условие отбора (Criteria) введите оператор Between 4 And 6, определяющий, попадает ли значение выражения в указанный интервал.
7. Запишите в вычисляемом поле выражение MonthName(Month(НАКЛАДНАЯ!ДАТА_ОТГ)) и убедитесь, что функция MonthName преобразует номер месяца в его полное на-звание.

Для закрепления смотрим видеоурок.

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

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

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

Выбор типа итога

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

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

Как работает "Сумма" и другие агрегатные функции

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

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

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

Функция	Описание	Поддерживаемые типы данных	Доступна в строке "Итог"?
	Вычисляет среднее значение для столбца. Столбец должен содержать числовые или денежные величины или значения даты или времени. Функция игнорирует пустые значения.
Количество значений	Подсчитывает число элементов в столбце.	Все типы данных, за исключением сложных повторяющихся скалярных данных, таких как столбец многозначных списков. Дополнительные сведения о многозначных списках можно найти в статье рекомендации по созданию и удалению многозначных полей в руководствах , посвященных многозначным полям.
Максимальное значение	Возвращает элемент с наибольшим значением. Для текстовых данных наибольшим будет последнее по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения.	, "Дата и время"
Минимальное значение	Возвращает элемент с наименьшим значением. Для текстовых данных наименьшим будет первое по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения.	"Числовой", "Действительное", "Денежный", "Дата и время"
Стандартное отклонение		"Числовой", "Действительное", "Денежный"
	Суммирует элементы в столбце. Подходит только для числовых и денежных данных.	"Число", "Действительное", "Денежный"
Дисперсия	Вычисляет статистическую дисперсию для всех значений в столбце. Подходит только для числовых и денежных данных. Если таблица содержит менее двух строк, Access возвращает пустое значение. Подробнее о функции "Дисперсия" см. в следующем разделе .	"Числовой", "Действительное", "Денежный"

Дополнительные сведения о функциях "Стандартное отклонение" и "Дисперсия"

Функции Стандартное отклонение и Дисперсия рассчитывают статистические значения. В частности, они применяются для значений, которые лежат в окрестности своего среднего значения и подчиняются закону нормального распределения (находятся на гауссовой кривой).

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

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

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

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

В этой статье

Типы агрегатов, которые можно добавить в отчет

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

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

Добавление суммы или другого агрегата в режиме макета

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

Данные (Control Source) выражение, которое выполняет нужное вычисление. Если в отчете есть уровни группировки, Access также добавит текстовое поле, которое выполняет такие же вычисления в каждом разделе примечания группы.

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

Добавление суммы или другого агрегата в режиме конструктора

Конструктор позволяет точнее настраивать размещение и внешний вид итоговых значений. В сгруппированных отчетах можно поместить итоги и другие агрегаты в заголовок или примечание каждой группы. Агрегаты уровня отчета можно поместить в заголовок или колонтитул отчета.

Access добавляет текстовое поле в колонтитул отчета и задает в качестве значения его свойства Данные (Control Source) выражение, которое выполняет нужное вычисление. Если в отчете есть уровни группировки, Access также добавит текстовое поле, которое выполняет такие же вычисления в каждом разделе примечания группы. Если для свойства Сумма с накоплением (Running Sum) задано значение Для всего , то общее итоговое значение можно повторить в колонтитуле отчета. Создайте в нем поле и задайте в качестве значения его свойства Данные (Control Source) имя поля, в котором вычисляется сумма с накоплением, например =[СуммаЗаказа] .

Люди! Помогите сделать простейший запрос.Имеется таблица: Таблица1 формата:Наименование заказа | Сумма | Общая сумма всех заказовпервый 10р. второй 20р.третий 30р.Скажите как мне сложить строки в столбце Сумма? Чтобы вывести результат "Общая сумма всех заказов" = 60р.И таблица со временем будет дописываться... т.е. появится и 100й заказ... соответственно у 100го заказа своя сумма. Надо чтоб "Общая сумма всех заказов" пересчитывалась автоматически.Заранее спасибо за помощь.

8 ответов

По указанным данным это невозможно.Чем определяется сортировка строк в таблице?

Вот в Excel это ведь просто делается. К примеру команда =СУММ(A1:A100) - т.е. получим суммы по 100 ячейкам таблице.Почему же в БД такого не сделать?Каждому наименованию заказа(текст) соответствует соответствующийц номер(число). Собственно эти числа(номера заказов) идут по порядку. Т.е. пример:1 | сумка | 100р. |2 | покрышка | 50р. |...100 | книга | 60р. |...Как мне посчитать общую сумму всех заказов(1...100...)?

mivang , В Excel существует физический порядок записей в таблице. Который определяет, что выше и что ниже. В реляционных БД такого порядка НЕ СУЩЕСТВУЕТ. Порядок определяется заданной в запросе сортировкой, и при ее отсутствии может быль ЛЮБЫМ. Хотите чтобы вопрос имел смысл - четко укажите критерий, на основе которого можно устновить порядок следования записей. В проведенном примере это, например, номер строки. Однако в структуре показанной таблицы такого поля нет.

Хотите чтобы вопрос имел смысл - четко укажите критерий, на основе которого можно устновить порядок следования записей. В проведенном примере это, например, номер строки. Однако в структуре показанной таблицы такого поля нет.

В таблице имеется поле №заказа. Т.е. общий вид примерно такой:№заказа | Наименование заказа | Сумма заказа |1 | сумка | 10rur |2 | krushka | 20rur |3 | banka | 30rur |... | ... |***|Теперь есть привязка? Или я опять чего то не понял... :(Как найти общую сумму всех заказов?

Теперь есть привязка?

Да. SELECT Таблица1.[№ заказа], Таблица1.[Наименование заказа], Таблица1.[Сумма заказа], Sum(q1.[Сумма заказа]) AS [Общая сумма всех заказов]FROM (SELECT t1.[№ заказа], t1.[Наименование заказа], t1.[Сумма заказа], t2.[№ заказа] FROM Таблица1 AS t1, Таблица1 AS t2 WHERE (((t1.[№ заказа])>=.[№ заказа]))) AS q1 INNER JOIN Таблица1 ON q1.t1.[№ заказа] = Таблица1.[№ заказа]GROUP BY Таблица1.[№ заказа], Таблица1.[Наименование заказа], Таблица1.[Сумма заказа];

SELECT Таблица1.[№ заказа], Таблица1.[Наименование заказа], Таблица1.[Сумма заказа], Sum(q1.[Сумма заказа]) AS [Общая сумма всех заказов]FROM [ SELECT t1.[№ заказа], t1.[Наименование заказа], t1.[Сумма заказа], t2.[№ заказа] FROM Таблица1 AS t1, Таблица1 AS t2 WHERE (((t1.[№ заказа])>=.[№ заказа]))]. AS q1 INNER JOIN Таблица1 ON q1.t1.[№ заказа] = Таблица1.[№ заказа]GROUP BY Таблица1.[№ заказа], Таблица1.[Наименование заказа], Таблица1.[Сумма заказа];

Выводится ошибка: Недопустимое использование скобок с именем "SELECT t1.[№ заказа". :((В чём проблема?Добавлено через 3 минуты и 33 секунды Народ! Готов выслать БДку. Если сделаете. С меня оплата в WM. ОЧЕНЬ СРОЧНО(в течении 1 часа)

Предположим у нас имеется следующая таблица:таблица: tblOrders поле тип idOrders СчетчикfldName ТекстовыйfldSumma ДенежныйТогда запрос будет примерно таким: SELECT Sum(tblOrders.fldSumma) AS FROM tblOrders; Sum-fldSumma это псевдоним выражения Sum(tblOrders.fldSumma) . В итоге мы получаем таблицу с одной ячейкой. Вот для разъяснения написал пример кода на VS 2005, без обработки исключений, чтобы было понятнее.Для начала нужно создать проект, и добавить туда кнопку GetSumButton и текстовое поле SumTextBox , то есть поменять их имена. Имя формы заменить на MainForm и заменить её код нижестоящим. Option Strict OnOption Explicit OnImports System.Data.OleDbPublic Class MainForm """

Получить сумму

Private Function GetSumma() As Decimal Using cn As New OleDbConnection(My.Settings.dbConnectionString) cn.Open() Dim cmd As OleDbCommand = cn.CreateCommand() cmd.CommandText = "SELECT Sum(tblOrders.fldSumma) AS FROM tblOrders;" Return CDec(cmd.ExecuteScalar()) End Using End Function """

Обработка события Click кнопки GetSumButton

Private Sub GetSumButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GetSumButton.Click " Выводим сумму в текущей валюте Me.SumTextBox.Text = FormatCurrency(GetSumma()) End SubEnd Class Чтобы пример заработал нужно в My Project >> Settings добавить строку с именем dbConnectionString следующего содержания Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\orders.mdb . Прикрепить к проекту базу данных MS Access или просто скопировать её в директорию с исполняемым файлом, внутри которой должна быть создана нужная таблица (желательно с данными).