Calculate a running sum (cumulative total)

You can use Access to create what is called 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.

Sales by product report with cumulative running sum

To create a running sum, you first add a text box to the report in Design view and set its ControlSource property to the field or expression that you want to sum (ExtendedPrice, for example). You then set the RunningSum property of the text box, which determines the range of records over which the text box values are accumulated. Here are the possible settings for the RunningSum property:

Property setting Description
No This is not a running sum.
Over Group The text box displays a running sum of the values in the same group level. The value accumulates until another group section is encountered.
Over All The text box displays a running sum of the values in the same group level. The value accumulates until the end of the report.

The Sales by Product with Running Sum report, created with data from the Northwind.mdb sample database, demonstrates this feature. It includes a text box called Cumulative, and its ControlSource property is set to ExtendedPrice. Its RunningSum property is set to Over Group, as shown here:

The Running Sum property for a text box

Step-by-step instructions

The following procedure provides step-by-step instructions for creating a running sum in a report.

To open the report in Design view    

  1. In the Database window, under Objects, click Reports.
  2. Click the report, and then click Design in the Database window.

To create the text box control    

  1. In the toolbox, click the Text Box tool.

 Note   If the toolbox is not visible, on the View menu, click Toolbox.

  1. Drag the pointer in the Detail section, Group Header, or Group Footer to create a text box in each section.

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

To set the property    

  1. Select the text box, and then click Properties on the View menu.
  2. Click the All tab, and then click the Control Source property box.
  3. 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, you could type the expression =Sum([ExtendedPrice]).

To set the property    

  1. Click the All tab, and then click the Running Sum property box.
  2. Click the arrow that appears, and then do one of the following:
    • If you want the running sum to reset when the next high level is reached, click Over Group in the list.
    • If you want the running sum to accumulate until the end of the report, click 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 (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.). Create a text box in the report footer and set its ControlSource property to the name of the text box that calculates the running sum; for example, =[OrderAmount].

 
 
Applies to:
Access 2003