Главная » Безопасность » Подсчет суммы полей в access. Суммирование в отчетах. Поддерживаемые типы данных

Подсчет суммы полей в access. Суммирование в отчетах. Поддерживаемые типы данных

Пример вычисления общей суммы в таблице базы данных Microsoft Access без использования SQL -запроса

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

В данной задаче, на примере таблицы Microsoft Access , вычисляется общая сумма без использования SQL -запроса. Дополнительно вычисляется среднее арифметическое значений ячеек заданного столбца.

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

Условие задачи

В результате формируется строка ConnectionString в компоненте ADOConnection1 (рис. 2). В этой строке указывается тип поставщика данных и полный путь к файлу базы данных.

Рис. 2. Строка ConnectionString компонента ADOConnection1

  1. Настройка компонент ADOConnection1, ADOTable1, DataSource1, DBGrid1.

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

– в компоненте ADOConnection1 свойство LoginPrompt = “false” (рис. 3) (отмена запроса имени пользователя и пароля для доступа к базе данных);

– в компоненте ADOTable1 свойство Connection = “ADOConnection1” (рис. 4);

– в компоненте DataSource1 свойство DataSet = “ADOTable1” (рис. 5);

– в компоненте DBGrid1 свойство DataSource = “DataSource1” (рис. 6);

– в компоненте ADOTable1 свойство TableName = “Worker” (рис. 7);

– в компоненте DBGrid1 из свойства Options установить опцию dgEditing = false (запрет внесения данных в ячейки таблицы непосредственно из сетки DBGrid1) (рис. 8).

Рис. 3. Свойство LoginPrompt компонента ADOConnection1

Рис. 4. Свойство Connection компонента ADOTable1

Рис. 5. Свойство DataSet компонента DataSource1

Рис. 6. Свойство DataSource компонента DBGrid1

Рис. 7. Свойство TableName компонента ADOTable1

Рис. 8. Опция dgEditing свойства Options компонента DBGrid1

  1. Активизация таблицы.

Для отображения данных в таблице нужно выполнить следующие действия (рис. 9):

– выделить таблицу ADOTable1;

– свойство Active = true.

После этого данные таблицы отобразятся в DBGrid1.

Рис. 9. Свойство Active компонента ADOTable1

  1. Настройка размеров компонент и формы.

Следующим шагом нужно настроить размеры и позиции компонент на форме, как изображено на рисунке 10.

Рис. 10. Основная форма приложения

  1. Скрытие поля ID_Worker в DBGrid1.

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

Сначала нужно вызвать редактор полей (“Fields Editor… ”) компонента ADOTable1 из контекстного меню (рис. 11).

Рис. 11. Вызов редактора полей компонента ADOTable1

Откроется окно Form1.ADOTable1. В этом окне, с помощью мышки, нужно вызвать контекстное меню. В контекстном меню выбрать команду «Add All fields ».

В результате, окно редактора примет вид, как изображено на рисунке 12.

Рис. 12. Редактор полей

В редакторе полей Form1.ADOTable1 отображаются все поля таблицы Worker. Чтобы удалить поле ID_Worker, нужно на строке ID_Worker сделать клик правой кнопкой «мыши» и в контекстном меню выбрать команду Delete. После этого можно закрыть редактор.

В результате, в таблице DBGrid1 будет отображено только два поля (рис. 13).

Рис. 13. Отображение таблицы Worker с двумя полями

  1. Установление фильтра вывода с двумя знаками после запятой в поле Salary.

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

Выделить компонент ADOTable1. Вызвать редактор полей “Fields Editor… ” так как описано в пункте 6. В результате откроется список из двух полей Name и Salary. Выделить строку с названием Salary. В Object Inspector будет активирован объект с именем ADOTable1Salary.

Следующим шагом нужно в Object Inspector в поле “Display Format ” установить значение «0.00 » (рис. 14).

Рис. 14. Установление формата вывода в поле Salary объекта ADOTable1

После выполненных действий, поле Salary в таблице будет выводиться с точностью 2 знака после запятой.

  1. Ввод внутренних переменных.

Для сохранения данных суммы и среднего арифметического нужно ввести в текст класса формы TForm1 внутренние переменные с именами sum и avg.

Переменные вводятся в раздел private. Фрагмент кода класса TForm1 формы имеет следующий вид:

... type TForm1 = class (TForm) ADOConnection1: TADOConnection; DataSource1: TDataSource; ADOTable1: TADOTable; DBGrid1: TDBGrid; StaticText1: TStaticText; StaticText2: TStaticText; StaticText3: TStaticText; ADOTable1Name: TWideStringField; ADOTable1Salary: TFloatField; procedure FormActivate(Sender: TObject); procedure FormClose(Sender: TObject; var Action: TCloseAction); private { Private declarations } sum:real; // сумма avg:real; // среднее арифметическое public { Public declarations } end ; ...
  1. Программирование события активизации формы.

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

Поэтому, нужно запрограммировать событие OnActivate формы Form1. Событие OnActivate вызовется в момент активизации формы после запуска программы на выполнение.

Пример программирования события в Delphi подробно описан .

В нашем случае обработчик события имеет следующий вид:

procedure TForm1.FormActivate(Sender: TObject); var f:TField; // дополнительная переменная типа «Поле» begin // 1. Проверка, есть ли записи в таблице if ADOTable1.RecordCount = 0 then exit ; // 2. Отключить визуализацию в DBGrid1 ADOTable1.DisableControls; // 3. Перебор всех записей таблицы Worker // 3.1. Перейти на первую запись ADOTable1.First; // 3.2. Обнулить сумму sum:= 0; // 3.3. Взять значение зарплаты из первой записи f:= ADOTable1.FieldByName("Salary"); // 3.4. Цикл перебора записей в таблице // - проверка на достижение конца таблицы while ADOTable1.Eof<>true do begin // 3.4.1. Увеличить сумму sum:= sum + f.Value; // 3.4.2. Перейти на следующую запись ADOTable1.Next; end ; // 4. Вычислить среднее арифметическое avg:= sum / ADOTable1.RecordCount; // 5. Заполнить строки типа TStaticText StaticText1.Caption:= "Сумма: " + FloatToStr(sum, ffFixed, 8, 2); StaticText2.Caption:= "Средняя зарплата: " + FloatToStr(avg, ffFixed, 8, 2); // 6. Включить визуализацию в DBGrid ADOTable1.EnableControls; end ;

Объясним некоторые фрагменты кода.

Методы DisableControls и EnableControls отвечают за отключение и включение подключенных к набору данных (базы данных) визуальных элементов управления. Вызов метода DisableControls позволяет значительно ускорить процесс перебора записей, поскольку заставит приложение не тратить время на перерисовывание содержимого элемента управления DBGrid1 с каждым изменением записи.

Свойство

ADOTable1.RecordCount

указывает число записей в таблице.

ADOTable1.First

устанавливает активным первую запись таблицы.

ADOTable1.FieldByName()

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

f.Value

Свойство

ADOTable1.Eof

становится равным true, если достигнут конец таблицы.

ADOTable1.Next

реализует переход на следующую запись таблицы.

Результат суммы выводится в свойстве Caption компонента StaticText1.

Значение среднего арифметического выводится в свойстве Caption компонента StaticText2.

  1. Запуск приложения на выполнение.

Теперь можно запустить приложение на выполнение.

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

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

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

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

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

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

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

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

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

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

Функция

Описание

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

Доступна в строке "Итог"?

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

Количество значений

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

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

Максимальное значение

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

, "Дата и время"

Минимальное значение

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

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

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

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

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

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

Дисперсия

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

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

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

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

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

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

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

Вопрос: Сумма столбцов в Access ?


В общем заданием является:
Подсчитать прибыль склада по каждому виду товара за последний год.
Виды товара определяются по коду из таблицы "Название". (То бишь вид = название)
Прибылью является поле "Сумма заказа" из таблицы "Заказ"

В итоге нужно вывести
Название -> Общая сумма

Ответ: Проблема решена.
Спасибо)

Вопрос: Mysql: Index по сумме столбцов


Всем привет.
Недавно начал изучать индексы в mysql. Столкнулся со следующем вопросом, ответ на который найти не смог.
Есть запрос на выборку, в котором учавствует условие по сумме столбцов, скажем:
Как правильно создать индекс для данного условия (col1+col2) ?
Я предположил, что можно сделать это так:
SQL
1 CREATE INDEX col1col2 ON table1(col1+ col2) ;

Но есть сомнения в правильности такой записи.
Кто-нибудь может подсказать, как правильно?

Ответ: Спасибо за предложения решения проблемы.

Вопрос: Выборка верхних строк с определенной суммой столбца


Есть таблица
Id Quantity
1 50
2 100
3 50
4 20
5 60
6 20
7 100

где Id 1 - самая новая запись, Id 7 - старая.
Нужно выбрать верхние строки с общей суммой столбца "Quantity" не менее 250, то есть ожидаемый результат:
Id Quantity
1 50
2 100
3 50
4 20
5 60

Сам пока дошел только до такого:
SELECT * FROM ( SELECT SUM (Quantity) OVER (ORDER BY Id ASC ) AS S, a.* FROM my_table a ) WHERE S < 250
В результате получаю меньше, чем нужно, на 1 строку:

Т.е. ты хочешь сказать что с ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING бeз NVL pаботало?
Помедитируй что вернет пeрвый ROW/RANGE при 1 PRECEDING.

SY.
Хм, без nvl теряет первый row/range, всё остальное исправно выводит. Да, сложно назвать это "работало".

Вопрос: access дата время


доброго времени суток, такой вопрос появился
создал базу в accses 2016 и столкнулся с проблемой что нужно учитывать выходные и праздники в вычисляемом столбце.
вычисляемый столбец считается как сумма столбца "дата начала командировки" + "количество дней командировки". нужно чтобы после сложения первых двух слагаемых проверялся день недели и если это четверг прибавлялось 5 дней если пятница 5 дней если суббота 4 дня а если остальные дни то 3 дня и после этого число вводилось в созданный под это столбец таблице. незнаю реально это или нет но всю голову уже сломал, если кто знает решение подскажите пожалуйста.

К сообщению приложен файл. Размер - 22Kb

Ответ: stalkermen4884,
-запрос работает как Вам надо?
-в модуле написаны функции, которые применены в запросе (посмотрите повнимательнее что написано в полях, в режиме конструктора) и используют в качестве аргументов поля запроса (st-поле[s],fin-поле)
-если хотите чтоб срок командировки считался включительно последний день во всех функциях исправьте i=0
-поставьте точку останова внутри функции и можете,выполняя её по шагам,посмотреть как она работает.
-читайте книШки (хотя-бы самоучители в сети)-там про все написано (если читать вдумчиво, разбирая написанное, всё поймёте-не буду же я Вам их пересказывать. Этот форум предполагает наличие базовых знаний)

Вопрос: Построчное суммирование столбца


Всем привет!

Заранее извиняюсь, но что-то не понимаю как посчитать построчно сумму столбца. В экселе все просто (во вложении).
А как это делается в access"e?

Заранее спасибо!

Ответ: Большое спасибо!

Добавлено через 23 часа 57 минут
В продолжении темы. Прошу помощи в оптимизации запроса с накапливанием итога.

Количество строк ZnachRURSum порядка 3000. Access очень долго считает. Есть способ ему помочь?

Ps: потом это все добро поедет на mysql.

Вопрос: Сумма столбцов


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

Ответ: Вячеслав Я , Ещё видел что можно и с помощью подчинённой формы сделать, пытался - таже мелодрамма.


Есть таблица User

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

Ответ:

Сообщение от cweic

Почему-то не работает твой запрос.

Возможно. Не проверял. Сейчас проверю.

Сообщение от cweic

Подскажи пожалуйста, что означает четвертая строка?

Выборка столбца SUM из таблицы reat_sum, полученная подзапросом.

Сообщение от cweic

И еще не понятно, на 16 строке псевдоним AS t1? Это имеет отношение к первой таблице?

T1 - название таблицы, которая будет возвращена в результате выполнения этого запроса:

Вопрос: Кто знает Access хорошо? Оформление страховки


"Форма" под названием "Оформление страховки" в Access состоит из нескольких полей
-Код клиента,
-Код страховки,
-ФИО клиента,
-Адрес клиента,
-Телефон,
-Код тарифа,
-Дата страхования,
-Сумма платежа,
-Название тарифа.

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

Причем Код тарифа и Сумма платежа есть в отдельной таблице "Тарифы страхования", а также в таблице "Оформление страховки", которая является исходным источником для "Формы" "Оформление страховки".

Люди пожалуйста помогите, кто знает.

Ответ: 1. В источник строк код тарифа добавляется 3 столбец с ценой тарифа, указывается, что столбцов 3, подставляется код (первый), виден второй (обозначение), первый и третий не видны - это определяется шириной столбцов (2см;0см;0см). Все это в конструкторе, свойствах поля со списком.
2. В событии после обновления Цене присваивается значение 2 колонки (3-его столбца, в VBA нумерация с 0), там прописана цена выбраного тарифа.

Вопрос: Запрос по столбцам


Здравствуйте.
Я задал вопрос
Тепер похожа тема только со столбцами.
Как сделать запрос на то чтобы подсчитывал например сумму в столбцах ОТ 3 столбца
например есть стоблик О4 и много таких столбиков например О5, О6, О7, и так много
и нада сумму О4,О5... но сумму не общою а по одельности но в одной таблице
По сути сумма столбцов от О4 и до конца (сколько есть даже если добавить новий)
в новой таблице должно быть названия столбца и его сумма.
Много текста но надеюсь понятно)

Ответ: alvk , ну вы прям с каждым разом всё больше шокирует своими познаниями.

Вопрос: Как сделать выпадающий список в Access в подключённой таблице из MySql?


ВОПРОС:
Как сделать выпадающий список в Access в ?
Какой должна быть талица справочника: столбцы(тип) - ? штук.
Где должен (или целесообразно) разместить справочник: в таблице справочнике Access или в отдельной подключённой таблице справочнике из MySql ?
Существую ли другие более грамотные способы реализации данной задачи?

ТРЕБУЕТСЯ:
Сделать выпадающий список в Access в подключённой таблице из MySql .
Характеристики выпадающего списка:
- кол. поз. < 10штук
- содержание: ВЫП СПС ПОЗ.1, ВЫП СПС ПОЗ.2, ВЫП СПС ПОЗ.3,... ВЫП СПС ПОЗ.10
Данные в выпадающий список должны попадать из:

или

по наиболее целесообразному сценарию.

УСТАНОВЛЕНО:
- КОМП 1
- установлен MySql с центральной БД.
- В базе данных (БД) имеется Таблица_1 со следующими столбцами:

- КОМП 2
- Access подключается через ODBC как клиент к БД MySql.

ТЕРМИНЫ:
- таблица справочник Access - Таблица справочник №1(см. скриншот);
- подключённая таблица справочник из MySql - Таблица справочник №2(см. скриншот);
- подключённая таблица из MySql - Таблица БД клиент (см. скриншот);

Ответ: Да вам что с нуля форму строить, что мой пример разбирать - по времени будет одинаково.

В панели переходов выделите основную таблицу. На ленте "создание" выберите кнопку "форма". Мастер создаст вам форму со всем полями вашей таблицы.

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

Щелкаете на этом поле правой кнопкой мышки и выбираете пункт "преобразовать поле в" - "поле со списком"

В получившемся поле со списком меняем свойстваа:
Источник данных - тут пишем имя таблицы или имя запроса или текст запроса
Привязанный столбец - там стоит 1, обычно так и надо (это номер столбца с иденификатором или кодом, если он у вас не 1й - можно или в запросе поменять порядок, или номер привязанного столбца исправить)
Число столбцов - как правило, 2, но может быть и больше, если надо
Ширина столбцов - обычно пишут 0;ШиринаКотораяНужна или просто 0 - тогда столбец с кодом не виден на экране, видны только значения. Если у вас другое чсило столбцов или другой порядок - ширины менются соответственно.

Ну и собственно все. Полем можно пользоваться.

Добавлено через 6 минут

Сообщение от texnik-san

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

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

Например, Вася случайно залез в справочник и стер там все значения (система позволила, спавочник-то локальный). Потом испугался, что будут ругать, и никому ничего не сказа, просто сел и набил все значения заново. Текст со старым совпадает. А что ключи разные - так Вася ж этого не знает. Потом начинается "Я выбрал то же, что и всегда выбирал, а программа пишет ошибку "нет такого значения в связанной таблице"". Думаете, так не бывает? Лично на такие грабли наступала

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

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

В этой статье

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

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

Вычисление

Описание

Функция

Суммирует элементы в столбце.

Определяет среднее значение всех элементов столбца.

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

Максимальное значение

Возвращает элемент, имеющий наибольшее (числовое или по алфавиту) значение в столбце.

Минимальное значение

Возвращает элемент, имеющий наименьшее (числовое или по алфавиту) значение в столбце.

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

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

Дисперсия

Вычисляет дисперсию для всех значений в столбце.

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

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

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

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

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

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

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

В этой статье речь поговорим про вычисляемые поля в запросах 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 преобразует номер месяца в его полное на-звание.


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



Предыдущая статья: Следующая статья:

© 2015 .
О сайте | Контакты | Карта сайта