Summing in reports

In any report that contains numbers, you can use totals, averages, percentages, or running sums to make the data more understandable. This article describes how to add these elements to your report.

In this article


Types of aggregates that you can add to a report

The following table describes the types of aggregate functions in Microsoft Office Access 2007 that you can add to a report.

Calculation Description Function
Sum The sum of all the numbers in the column. Sum()
Average The average value of all the numbers in the column. Avg()
Count The count of items in the column. Count()
Maximum The highest numeric or alphabetic value in the column. Max()
Minimum The lowest numeric or alphabetic value in the column. Min()
Standard Deviation An estimate of the standard deviation across the set of values in the column. StDev()
Variance An estimate of the variance across the set of values in the column. Var()

Top of Page Top of Page

Add a total or other aggregate in Layout view

Layout view provides you with the quickest way to add totals, averages, and other aggregates to your report.

  1. In the Navigation Pane, right-click the report and then click Layout ViewButton image.
  2. Click the field you want to summarize. For example, if you want to add a total to a column of numbers, click one of the numbers in the column.
  3. On the Format tab, in the Grouping & Totals group, click Totals.Button image
  1. Click the type of aggregate that you want to add for the field.

Office Access 2007 adds a text box to the Report Footer (report footer: A report section that is used to place information that normally appears at the bottom of the page, such as page numbers, dates, and sums.) section and sets its Control Source property to an expression that performs the calculation you want. If there are any grouping levels in your report, Access also adds a text box that performs the same calculation to each Group Footer (group footer: Used to place information, such as group name or group total, at the end of a group of records.) section.

For more information about how to create grouping levels in reports, see the article Create a grouped or summary report.


The following video shows you how to add a total in Layout view.

Show me >> Adding totals to a report in Layout view

                             Video created by Office Online staff writers

Assistance Problems watching the video? Try our troubleshooting tips.


Top of Page Top of Page

Add a total or other aggregate in Design view

Design view gives you a bit more control over the placement and appearance of your totals. In grouped reports, you can put totals or other aggregates in the header or footer of each group. Report-wide aggregates can be placed in the Report Header (report header: A report section that is used to place information (such as a title, date, or report introduction) at the beginning of a report.) or Report Footer section.

  1. In the Navigation Pane, right-click the report and then click Design ViewButton image.
  2. Click the field you want to summarize. For example, if you want to add a total to a column of numbers, click one of the numbers in the column.
  3. On the Design tab, in the Grouping & Totals group, click Totals Button image.
  4. Click the type of aggregate that you want to add for the field.

Office Access 2007 adds a text box to the Report Footer (report footer: A report section that is used to place information that normally appears at the bottom of the page, such as page numbers, dates, and sums.) section and sets its Control Source property to an expression that performs the calculation you want. If there are any grouping levels in your report, Access also adds a text box that performs the same calculation to each Group Footer (group footer: Used to place information, such as group name or group total, at the end of a group of records.) section.

For more information about how to create grouping levels in reports, see the article Create a grouped or summary report.


The following video shows you how to add a total in Design view.

Show me >> Adding totals to a report in Design view

                             Video created by Office Online staff writers

Assistance Problems watching the video? Try our troubleshooting tips.


Top of Page Top of Page

Calculate a running sum (cumulative total)

You can use Office Access 2007 to create a running sum in a report. A running sum is a total that is accumulated from record to record across a group, or even across the entire report.

Running sum in a report

Create a running sum

  1. In the Navigation Pane, right-click the report and then click Design ViewButton image .
  2. On the Design tab, in the Controls group, click Text Box.Button image
  1. Click in either the Detail section, a Group Header section, or a Group Footer section to create a text box.

If a label appears next to the text box, either delete the label or change its text to a meaningful value.

  1. Select the text box. If the property sheet is not already displayed, press F4 to display it.
  2. Click the Data tab. In the Control Source property box, type the field name or expression for which you want to create the running sum.

For example, type ExtendedPrice for the ExtendedPrice field or, at the group level, type the expression =Sum([ExtendedPrice]).

  1. Click the Running Sum property box.
  2. Click the drop-down arrow in the property box, and then use one of the following procedures:
    • If you want the running sum to reset to 0 when the next higher grouping level is reached, select Over Group in the list.
    • If you want the running sum to accumulate until the end of the report, select Over All in the list.
  3. Close the property sheet.

 Note   When you set the RunningSum property to Over All, you can repeat the grand total in the Report Footer section. Create a text box in the report footer and set its Control Source property to the name of the text box that calculates the running sum; for example, =[OrderAmount].

Top of Page Top of Page

 
 
Applies to:
Access 2007