Microclimate.su

IT Новости
12 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как посчитать сумму в аксессе

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

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

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

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

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

Сумма для суммирования столбцов чисел;

Среднее для вычисления среднего значения в столбце чисел;

Максимум для нахождения наибольшего значения в поле;

Минимум для нахождения наименьшего значения в поле;

Стандартное отклонение для оценки разброса значений относительно среднего значения;

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

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

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

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

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

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

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

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

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

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

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

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

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.

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

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

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

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

Каждое поле отображается в столбце в бланке запроса.

На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

Результаты запроса отображаются в режиме таблицы.

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

При необходимости вы можете сохранить запрос.

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

Откройте запрос в режиме таблицы. Если база данных имеет формат ACCDB, щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Режим таблицы.

Если используется база данных в формате MDB, созданная в более ранней версии Access, на вкладке Главная в группе Режимы щелкните стрелку под кнопкой Режим и выберите значение Режим таблицы.

Дважды щелкните запрос в области навигации. Запрос будет выполнен, а его результаты будут загружены в таблицу.

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

Под последней строкой данных в таблице появится новая строка Итог.

Читать еще:  Как создать базу данных в аксесс

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

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

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

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

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

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

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

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

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.

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

Таблица появится в окне в верхней части конструктора запросов.

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

На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги.

В бланке появится строка Итог, а в строке для каждого поля запроса будет указано Группировка.

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

На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

Результаты запроса отображаются в режиме таблицы.

При необходимости вы можете сохранить запрос.

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

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.

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

Таблица (или таблицы) появится в окне в верхней части конструктора запросов.

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

На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги.

В бланке появится строка Итог, а в строке для каждого поля запроса будет указано Группировка.

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

На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

Результаты запроса отображаются в режиме таблицы.

При необходимости вы можете сохранить запрос.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Создание запроса с итоговыми суммами в Microsoft Access

Advanced: требуется написание экспертов, взаимодействие и многоязыковые навыки.

Эта статья относится к файлу базы данных Microsoft Access (. mdb) или к файлу базы данных Microsoft Access (ACCDB).

Сводка

В этой статье описываются два метода, которые можно использовать для создания запроса итогов с накоплением. Итоговый запрос итогов — это запрос, в котором итоговое значение каждой записи является суммированием этой записи и всех предыдущих записей. Этот тип запроса полезен для отображения совокупных итогов для группы записей (или за период времени) на графике или отчете.

Читать еще:  Как сделать составной ключ в аксесс

Note (Примечание ) Вы видите демонстрацию методики, используемой в этой статье, в примере файла QrySmp00. exe.

Дополнительные сведения

Способ 1

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

Следующий пример запроса использует таблицу Orders из учебной базы данных Northwind для создания суммы стоимости доставки за каждый месяц 1997. В целях повышения производительности примеры данных ограничены одним годом. Так как функция DSum выполняется один раз для каждой записи в запросе, для завершения обработки запроса может потребоваться несколько секунд (в зависимости от скорости компьютера). Чтобы создать и запустить этот запрос, выполните указанные ниже действия.

Откройте учебную базу данных Northwind.

Создайте новый запрос на выборку и добавьте таблицу Orders .

В меню вид выберите пункт итоги.

Note (Примечание ) В Access 2007 щелкните итоги в группе Показать/скрыть на вкладке конструктор .

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

В выражении в поле поле отображается и сортируется часть года поля OrderDate.

Во втором столбце бланка запроса введите в поле поле следующее выражение и выберите следующие параметры для полей «Итого», «Сортировать» и «Показать»:

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

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

Note (Примечание ) В следующем примере знак подчеркивания (_) в конце строки используется в качестве символа продолжения строки. При повторном создании этого примера удалите знак подчеркивания в конце строки.

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

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

Выражение в поле поля отображает каждый месяц в текстовом формате, например Январь, фев, Мар и т. д.

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

Выражение в поле поля фильтрует набор записей запроса, чтобы включить данные только из 1997.

Выполните запрос. Обратите внимание, что в поле Рунтот отображаются следующие записи с накопленной суммой:

Способ 2

Второй метод использует итоговый запрос с функцией DSum () для создания итоговой суммы по группе.

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

Откройте учебную базу данных Northwind. mdb.

Создайте новый запрос на выборку и добавьте таблицу Orders.

В Виевмену щелкните итоги.

Note (Примечание ) В Access 2007 щелкните итоги в группе Показать/скрыть на вкладке конструктор .

В первом столбце бланка запроса добавьте следующее поле в поле поле и выберите следующие параметры для полей «Итого» и «Показать»:

В этом поле данные группируются по КодСотрудника.

Во втором столбце бланка запроса добавьте следующее поле в поле поле и выберите следующие параметры для полей «Итого» и «Показать»:

Это поле суммирует данные доставки.

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

Note (Примечание ) В следующем примере знак подчеркивания (_) в конце строки используется в качестве символа продолжения строки. При повторном создании этого примера удалите знак подчеркивания в конце строки.

Выражение в поле поле использует функцию DSum () для суммирования поля фрахта, когда EmployeeID меньше текущего Емпалиас, а затем форматирует поле в долларах.

Выполните запрос. Обратите внимание, что в поле Рунтот отображаются следующие записи с накопленной суммой:

Вычисления в Access 2003

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

· Выражение. Выполнение вычислений в поле по формуле.

· Суммирование (Sum). Суммирует все значения выбранного поля.

· Среднее значение (Avg). Вычисляет среднее значение всех чисел в выбранном поле.

· Минимум (Min). Определение наименьшего числа в поле.

· Максимум (Max). Определение наибольшего числа в поле.

Рис.19. Открываем окно со строкой Групповые операции

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

Читать еще:  База данных аксесс скачать

Рис.20. Строка Групповая операция

Строка Групповая операция должна появиться автоматически. В некоторых версиях Accessэтастрока автоматически не появляется. В таком случае надо в панели инструментов нажать кнопку с символом ∑ (Групповая операция), которая выделена на рисунке . Для записи формулы выбираем пустое поле. В строке Групповая операция во всех полях запроса по умолчанию записано значение Группировка. Для ввода формулы в выбранном поле этой строки надо поместить значение Выражение, что можно сделать следующим образом:

· Щелкните по строке Групповая операция в выбранном поле.

· Щелкните по появившейся в поле кнопке с треугольником.

· В раскрывшемся списке выберите строку Выражение (рис.21 ).

· В строке Вывод на экран надо отметить флажок.

Рис. 21. Выбор групповой операции «Выражение»

Формула записывается в строке Поле по тем же правилам, что и в Excel, но вместо адресов ячеек используются заключенные в квадратные скобки имена полей. Имя поля в формуле следует вводить с учетом регистра, вручную, не копировать! В формулу могут входить числа (константы), однако проценты необходимо преобразовать в десятичные дроби. Например, если требуется вычислить отпускную цену товара, увеличив оптовую цену на 25%, в строке Поле следует записать формулу:

После ввода формулы щелкните за пределами готового выражения, можно в другой строке. Вместо знака равенства Accessдобавит в начало формулы текст Выражение1:, который и станет именем поля. Этот текст можно позднее заменить. Если в запрос будет добавлено еще одно поле с формулой, оно получит имя Выражение2: и т.д. Откройте запрос и проверьте наличие нового поля с заголовком Выражение 1. Для того, чтобы просмотреть запрос в виде таблицы, выберите в меню команду Вид, в раскрывшемся списке – Режим таблицы. Для изменения заголовка поля щелкните в панели инструментов на кнопке Конструктор(рис. 19), затем в нужном столбце строки Поле (рис. 20) удалите прежнее имя и введите новое.

Дата добавления: 2015-05-09 ; Просмотров: 976 ; Нарушение авторских прав?

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

SQL — Урок 11. Итоговые функции, вычисляемые столбцы и представления

Теперь, мы хотим узнать, на какую сумму нам привез товар поставщик «Дом печати» ( >
1. Сначала надо из таблицы Поставки (incoming) выбрать идентификаторы ( > SELECT >

2. Теперь из таблицы Журнал поставок (magazine_incoming) надо выбрать товары ( > SELECT >

3. Теперь нам надо добавить в результирующую таблицу цены на найденные товары, которые хранятся в таблице Цены (prices). То есть нам понадобится объединение таблиц Журнал поставок (magazine_incoming) и Цены (prices) по столбцу > SELECT magazine_incoming. >

4. В получившейся таблице явно не хватает столбца Сумма, то есть вычисляемого столбца . Возможность создания таких столбцов предусмотрена в MySQL. Для этого надо лишь указать в запросе имя вычисляемого столбца и что он должен вычислять. В нашем примере такой столбец будет называться summa, а вычислять он будет произведение столбцов quantity и price. Название нового столбца отделяется словом AS:

5. Отлично, нам осталось лишь просуммировать столбец summa и наконец-то узнаем, на какую сумму нам привез товар поставщик «Дом печати». Синтаксис для использования функции SUM() следущий:

Имя столбца нам известно — summa, а вот имени таблицы у нас нет, так как она является результатом запроса. Что же делать? Для таких случаев в MySQL существуют Представления . Представление — это запрос на выборку, которому присваивается уникальное имя и который можно сохранять в базе данных, для последующего использования.

Синтаксис создания представления следующий:

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

Вычисляемые поля (столбцы)

Представления

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

Но следует помнить, что представления — это не таблицы, то есть они не хранят данные, а лишь извлекают их из других таблиц. Отсюда, во-первых, при изменении данных в таблицах, результаты представления так же будут меняться. А во-вторых, при запросе к представлению происходит поиск необходимых данных, то есть производительность СУБД снижается. Поэтому злоупотреблять ими не стоит.

Видеоуроки php + mysql

Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.

Ссылка на основную публикацию
Adblock
detector