Letysite.ru

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

Как в access посчитать сумму столбца

Суммирование в отчетах

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

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

В этой статье

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

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

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

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

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

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

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

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

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

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

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

В области навигации щелкните отчет правой кнопкой мыши и выберите команду Макет.

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

На вкладке Конструктор в группе Группировка и итоги щелкните Итоги.

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

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

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

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

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

В области навигации щелкните отчет правой кнопкой мыши и выберите команду Режим конструктора.

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

На вкладке Конструктор в группе Группировка и итоги щелкните Итоги.

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

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

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

Вычисление сумм с накоплением (сумм с нарастающим итогом)

В Access можно создавать в отчетах суммы с накоплением. Сумма с накоплением — это сумма значений из всех записей группы или даже отчета.

Создание суммы с накоплением

В области навигации щелкните отчет правой кнопкой мыши и выберите команду конструктор .

На вкладке Конструктор в группе Элементы управления щелкните Поле.

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

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

Выделите текстовое поле. Если страница свойств не отображается, нажмите клавишу F4.

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

Например введите РасширеннаяЦена для поля «РасширеннаяЦена» или на уровне группы введите выражение =Sum([РасширеннаяЦена]).

Щелкните ячейку свойства Сумма с накоплением (Running Sum).

Читать еще:  Ошибка 1335 при установке офиса

Щелкните стрелку раскрывающегося списка в поле свойства и выполните одно из указанных ниже действий.

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

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

Закройте страницу свойств.

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

IT IS MY LIFE…

Уверен, кому-то приведенная здесь задача покажется простой, ну а для тех, кто как я пользуется MS Access от случая к случаю, может и пригодиться.

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

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

Затем необходимо перейти в режим SQL-запросов и после SELECT указать оператор DISTINCT должно получиться вот так:

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

В контекстном меню выбираем Итоги :

Делаем группировку по первому столбцу, подсчет ( Count ) по второму и для второго же столбца в качестве условия отбора ставим «больше единицы»:

Запускаем запрос и получаем решение задачи:

Т.е. в нашем примере для BBB и CCC есть два различных значения во втором столбце. Если бы у нас была потребность определить разные значения какого-либо параметра не в рамках значений какого-то одного столбца, а в рамках группы значений разных столбцов, то, соответственно, группировку следовало бы указать для всех этих столбцов, а Count, как и в данном примере, только для подсчитываемого параметра.

В общем-то на этом все. Можно еще вывести информацию о том, какие именно разные значения второго столбца соответствуют BBB и CCC . Объединяем последний запрос с исходной таблицей:

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

Теперь, мы хотим узнать, на какую сумму нам привез товар поставщик «Дом печати» (id=2). Составить такой запрос не так просто. Давайте поразмышляем, как его составить:

1. Сначала надо из таблицы Поставки (incoming) выбрать идентификаторы (id_incoming) тех поставок, которые осуществлялись поставщиком «Дом печати» (id=2):

2. Теперь из таблицы Журнал поставок (magazine_incoming) надо выбрать товары (id_product) и их количества (quantity), которые осуществлялись в найденных в пункте 1 поставках. То есть запрос из пункта 1 становится вложенным:

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

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

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

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

Читать еще:  Интервал в опен офисе

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

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

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

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

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

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

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

Итоговый запрос из базы данных Access

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

1. В окне базы данных щелкните на кнопке Запросы.

2. Дважды щелкните на значке Создание запроса в режиме конструктора.

3. В открывшемся окне диалога (рис. 17.6) выделите строку Контакты.

4. Щелчком на кнопке Добавить добавьте выбранную таблицу в верхнюю область конструктора запроса.

5. Выделите пункт Список и снова щелкните на кнопке Добавить.

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

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

Рис. 17.6 . Добавление таблицы

8. Перетащите поле Фамилия в ячейку Поле первого столбца конструктора.

9. В ту же ячейку второго столбца перетащите поле Имя таблицы Контакты.

10. В третий, четвертый и пятый столбцы бланка запроса перетащите поле Дата таблицы Список (рис. 17.7).

Рис. 17.7. Запрос с групповыми операциями

11. В раскрывающемся списке ячейки Групповая операция третьего столб ца бланка запроса выберите пункт Min.

12. В той же ячейке четвертого столбца выберите пункт Мах.

13. В пятом столбце задайте групповую операцию Count. Групповые операции построенного запроса обработают все записи таблицы Список, соответствующие конкретному человеку из таблицы Контакты, и вместо самих данных таблицы Список выведут в соответствующее поле результата запроса только значение величины, вычисляемой по определенной формуле. Доступные групповые операции перечислены в табл. 17.1.

ТАБЛИЦА 17.1 . Групповые операции

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

14. Щелкните на кнопке Вид, чтобы выполнить запрос. Появится таблица с пятью столбцами. Два первых столбца содержат фамилии и имена людей. По ним выполняется группировка, то есть расчет значений остальных полей запроса выполняется для записей таблицы Список, сопоставляемых с одним человеком. Как уже говорилось выше, соответствие контакта таблицы Список и человека из таблицы Контакты определяется полями Код_Контакты, с помощью которых осуществляется связь этих двух таблиц. Третий и четвертый столбцы запроса выводят соответственно дату первого (функция Min) и последнего (функция Мах) контакта с данным человеком. Пятый столбец содержит количество записей в таблице Список (функция Count), соответствующих. данному человеку, то есть число контактов с ним. Единственный недостаток построенного запроса — это непонятные имена столбцов. Давайте скорректируем их.

15. Щелчком на кнопке Вид вернитесь в конструктор запроса.

Читать еще:  Ключ для офис 7

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

17. В ячейке Поле четвертого столбца введите Дата последнего контакта: Дата.

18. В первой строке пятого столбца бланка запроса введите Число контактов: Дата.

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

19. Снова щелкните на кнопке Вид.

20. Закройте запрос.

21. Для сохранения изменений структуры щелкните на кнопке Да.

22. В окне диалога Сохранение введите имя Итоговый запрос и щелкните на кнопке ОК.

Добавление сумм, счетчиков и итогов в отчет

Access позволяет добавлять в отчет суммы, счетчики и итоги. Предположим, в конец отчета нужно добавить сумму объемов заказов. Для этого в примечан ии отчета нужно создать вычисляемый элемент управления. Источником данных вычисляемого элемента управления является выражение, а не поле. При открытии отчета с вычисляемым элементом управления Access вычисляет его значение на основе текущих данных. Например, чтобы напечатать на отчете текущую дату, в текстовое поле вводится выражение =Date(). Date — это встроенная функция, которая возвращает текущую дату по показаниям системных часов компьютера. При запуске отчета рассчитывается значение выражения в вычисляемом элементе управления, Date(), и текущая дата отображается в элементе управления отчета.

Использование функции «Sum»

Чтобы вычислить сумму объемов заказов по всему отчету, поместите в примечании отчета текстовое поле и присвойте свойству ControlSource текстового поля следующее выражение:

Функция Sum вычисляет итог по набору значений источника записей — в данном случае, столбца «Объем заказов». Sum — статистическая функция Access, которая позволяет вычислять итоговые значения.

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

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

Обратите внимание: если итоги будут подводиться в вычисляемом элементе управления, необходимо повторить выражение, записанное в вычисляемом элементе управления, в функции Sum.Например,если в вычисляемом элементе управления«Налог»записано выражение

=[Цена за единицу]*0,085,

чтобы рассчитать итог по значениям элемента управления «Налог», нужно записать

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

Использование функции «Count»

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

Звездочка означает, что Access будет проводить подсчет п о всем строкам области сведений, но результат будет зависеть от того, где будет размещено текстовое поле. Как и при подсчете суммы, способ работы счетчика в Access зависит от того, в каком разделе отчета будет находиться текстовое поле. Если разместить текстовое поле в заголовке или примечании группы, счетчик обработает только строки этой группы. Если текстовое поле будет находиться в заголовке или примечании отчета, счетчик обработает строки по всему отчету. На следующей диаграмме выражение «Count(*)» отображается в поле свойства ControlSource текстового поля, а итог, полученный по результатам работы функции Count, — в примечании отчета.

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