Summing in reports

In any Access 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

Add a total or other aggregate in Layout view

Add a total or other aggregate in Design view

Calculate a running sum (cumulative total)

Types of aggregates that you can add to a report

The following table describes the types of aggregate functions in Access 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()

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 View .

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.

   Note, if you're using Access 2007, on the Format tab, in the Grouping & Totals group, click Totals.

4. Click the type of aggregate that you want to add for the field.

Access adds a text box to the Report Footer 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 section.

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

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 or Report Footer section.

1. In the Navigation Pane, right-click the report and then click Design View .

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.

   Note, if you're using Access 2007, on the Format tab, in the Grouping & Totals group, click Totals.

4. Click the type of aggregate that you want to add for the field.

Access adds a text box to the Report Footer 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 section.

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

Calculate a running sum (cumulative total)

You can use Access 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.

Create a running sum

1. In the Navigation Pane, right-click the report and then click Design View .

2. On the Design tab, in the Controls group, click Text Box.

3. 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.

4. Select the text box. If the property sheet is not already displayed, press F4 to display it.

5. 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]).

6. Click the Running Sum property box.

7. 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.

8. 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