Summing in reports

by Sal Ricciardi

A sales report can include a lot of numbers. That's why you'll need to calculate sums at the appropriate locations in a report. In this article, learn how to place calculated sums in your reports. We provide a downloadable database with examples.

Applies to
Microsoft Office Access 2003
Microsoft Access 2000 and 2002

Question: How do I display totals at the end of my report?

You create a calculated control in the report footer. A calculated control is one whose source of data is an expression rather than a field. When you open a report that contains a calculated control, Access calculates the correct value by using the current data. For example, to print the current date on a report, you enter the expression =Date() in a text box. Date is a built-in function that returns the current date as stored in your computer's system clock. When Access runs this report, the expression in the calculated control, Date(), is evaluated, and the current date is displayed in the control on the report.

 Tip    Try Office 2010 Grouping and summing in reports is easier than ever in Access 2010!
Read an article or try Office 2010.

Calculating group or report totals

To calculate the sum of the quantities ordered for the entire report, you place a text box control in the report footer and set the ControlSource property of the text box to the following expression:

=Sum([QuantityOrdered])

The Sum function calculates the total for a set of values from your record source — in this case, the column named QuantityOrdered. Sum is one of the aggregate functions in Access that you can use to calculate summary values.

Most totals are generated by calculating a sum, either for a group of records or for all records. When you calculate a sum, the section of the report in which you place the calculated control containing the sum expression is important. Access determines how to aggregate the sum based on where you place the control — that is, based on what section of the report you choose.

Sales by product report design

For example, to calculate a total for one record, you create a calculated text box in the Detail section. To calculate a total for a group of records, such as for each product in a report that’s grouped by product, you create a calculated text box in the Group Header or Group Footer section for that group. To print a grand total for the entire report, you create a calculated text box in the Report Header or Report Footer section of the report.

Image of report showing group totals and report totals

For detailed step-by-step instructions on how to create a calculated control for a total in the report footer, see the following expandable section:

ShowStep-by-step: How to place a calculated control for a total in a report footer

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. On the View menu, select Report Header/Footer. Make sure that it remains selected. This will ensure that the report header and footer sections appear in the report design.
  2. 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 report footer to create the text box. If a label appears next to the text box, delete the label.

To set the ControlSource property of the text box    

  1. Right-click the text box, and then click Properties on the shortcut menu.
  2. Click the Data tab.
  3. To change the value of the ControlSource property, in the Control Source property box, type =Sum([QuantityOrdered]), where QuantityOrdered is the name of the field that you want to sum.
  4. Close the property sheet.

Understanding the report sections

Access divides the design for a report into sections. The section in which you choose to place a calculated control determines how Access calculates results for aggregate functions such as Sum. Here’s a summary of the section types and their uses:

  • Report Header    Printed once at the beginning of the report. Use the report header for information that might normally appear on a cover page, such as a logo or title and date. When you place a calculated control that uses the Sum aggregate function in the report header, the sum calculated is for the entire report. The report header prints before the page header.
  • Page Header    Printed at the top of every page. Use a page header, for example, to repeat the report title on every page. Aggregate functions do not work at the page level, but you can create page-level sums by using another technique shown later in this article.
  • Group Header    Printed at the beginning of each new group of records. Use the group header to print the group name. For example, in a report that is grouped by product, use the group header to print the product name. When you place a calculated control that uses the Sum aggregate function in the group header, the sum is for the current group.
  • Detail    Printed once for every row in the record source. This is where you place the controls that make up the main body of the report.
  • Group Footer    Printed at the end of each group of records. Use a group footer to print summary information for a group. When you place a calculated control that uses the Sum aggregate function in the group footer, the sum calculated is for the current group.
  • Page Footer    Printed at the end of every page. Use a page footer to print page numbers or per-page information. You must use the technique shown later in this article to print a sum per page.
  • Report Footer    Printed once at the end of the report. Use the report footer to print report totals or other summary information for the entire report. Note that the report footer appears last in the report design but prints before the final page footer. When you place a calculated control that uses the Sum aggregate function in the report footer, the sum calculated is for the entire report.

Placing sums in the page header or footer

Sometimes you want to calculate a total for each page of a report. The page footer and header sections, however, do not support calculated controls that use aggregate functions such as Sum. You can solve this problem with a little understanding of how reports work in Access, and a small amount of Microsoft Visual Basic® for Applications (VBA) code. VBA is the programming language that Access uses.

Access divides a report into sections and processes each section in turn according to its type. The Report Header section, for example, is printed once, at the beginning of the report. The Page Header section, on the other hand, is printed at the top of every single page. Because reports often have many pages, the Page Header section tends to be called often. Normally, the most often called section is the Detail section because it's called once for every row in the report's record source.

So, when you run a report, the Access report engine gets busy processing the report sections, many of them repeatedly. While it's processing report sections, Access also triggers a series of events (event: An action recognized by an object, such as a mouse click or key press, for which you can define a response. An event can be caused by a user action or a Visual Basic statement, or it can be triggered by the system.) that can be responded to by VBA code. For example, the section's Format event occurs when Access has selected the data to go in a section, but before the data is actually formatted or printed. You can use the Format event to intervene when you want to change a section layout on a page.

After the code in the Format event has been run, Access then formats the data and readies it for printing. At this point, Access triggers the section's Print event. The Print event is a good place to perform calculations that are based on data that will appear on the current page because you're guaranteed the data will actually be printed on that page. There's no such guarantee with the Format event because Access can determine that the section won't fit on the current page.

To calculate and print page totals, you can use the Print event for the Detail section and for the Page Header section, in combination with two text box controls that you insert in the Page Footer section. This technique consists of the following steps:

  • You create a text box in the Page Footer section to hold the page total for the dollar sum of sales, and another text box in the Page Footer section to hold the page total for the quantity of products sold. Let's call these values txtPageSum and txtPageQuantity.
  • You place some VBA code in the Print event for the Page Header section that will reset the value for each of these text boxes to zero. In this way, you initialize the page totals at the top of each page.
  • You place some VBA code in the Print event for the Detail section to add the values for the current record to the page sum and page quantity. This is where you accumulate the values for the current page.

Here's the code for the Print event for the Detail section (called Detail_Print) and the Print event for the Page Header section (called PageHeaderSection_Print):

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  If PrintCount = 1 Then
    txtPageSum = txtPageSum + ExtendedPrice
    txtPageQuantity = txtPageQuantity + Quantity
  End If
End Sub

Private Sub PageHeaderSection_Print(Cancel As Integer, _
                                    PrintCount As Integer)
  txtPageSum = 0
  txtPageQuantity = 0
End Sub

The PageHeaderSection_Print routine is called by the Print event of the page header at the top of every printed page. Its job is to reset the two page totals, txtPageSum and txtPageQuantity, to zero so that every page begins anew with fresh, initialized page totals. The Detail_Print routine is called by the Print event of the Detail section for each detail record. Its job is to add the values for that record to the page total. So, the value of ExtendedPrice is added to the value of txtPageSum to accumulate the page total for the sales amount, and the value of Quantity is added to the value of txtPageQuantity to accumulate the page total for quantity.

Note that the value of the PrintCount property is checked, before any accumulating begins, to determine if it's set to one. Access increments this property by one whenever the data for the current section is printed. Because there are times when the Print event for the Detail section for a particular record might be called more than once, checking the PrintCount value ensures that you don't add the same value twice to a page total.

Image showing the Sales by product with page total report

You can download the sample database for this article. The Sales by Product with Page Total report in the sample database uses the technique shown earlier in this article for the page totals. For detailed step-by-step instructions on how to create calculated page totals yourself, see the following expandable section:

ShowStep-by-step: How to calculate page totals 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 controls    

  1. On the View menu, select Page Header/Footer. Make sure that it remains selected. This will ensure that the Page Header and Page Footer sections appear in the report design.
  2. 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 page footer to create a text box. If a label appears next to the text box, delete the label.
  2. Select the text box, and then click Properties on the View menu.
  3. Click the All tab, and then type txtPageSum in the Name property box.
  4. Close the property sheet.
  5. Drag the pointer in the page footer to create a second text box. If a label appears next to the text box, delete the label.
  6. Select the text box, and then click Properties on the View menu, .
  7. Click the All tab, and then type txtPageQuantity in the Name property box.
  8. Close the property sheet.

To set the page header Print property    

  1. Double-click the page header border. The property sheet appears.
  2. Make sure that the title bar of the property sheet includes "PageHeaderSection." If it does not, click the All tab, click the Name property box, click the arrow that appears, and then click PageHeaderSection in the list.
  3. Click the All tab, click the On Print property box, and then click the ellipsis button (...) that appears.
  4. In the Choose Builder dialog box, click Code Builder, and then click OK.
  5. Edit the PageHeaderSection_Print procedure so that it appears exactly as shown earlier in this article.
  6. On the File menu, click Close and Return to Microsoft Access.
  7. Close the property sheet.

To set the Detail section Print property    

  1. Double-click the border of the detail section. The property sheet appears.
  2. Make sure that the title bar of the property sheet includes "Detail." If it does not, click the All tab, click the Name property box, click the arrow that appears, and then click Detail in the list.
  3. Click the All tab, click the On Print property box, and then click the ellipsis button (...) that appears.
  4. In the Choose Builder dialog box, click Code Builder, and then click OK.
  5. Edit the Detail_Print procedure so that it appears exactly as shown earlier in this article.
  6. On the File menu, click Close and Return to Microsoft Access.
  7. Close the property sheet.

Calculating running sums (cumulative totals)

With Access you can create what is called a running sum. A running sum is a total that is accumulated from record to record across a group, or even across the entire report.

Image showing sales by product report with cumulative running sum

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

Running Sum property values    

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

To demonstrate this feature, I created the Sales by Product with Running Sum report in the sample database. I added a text box called Cumulative, and set its ControlSource property to ExtendedPrice. Then I set its RunningSum property to Over Group, as shown here:

Image showing the running sum property

For step-by-step instructions to create a running sum, see the following expandable section:

ShowStep-by-step: How to create 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 ControlSource 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 RunningSum 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.

Tip: Numbering the rows

Here's a tip related to using the RunningSum property. Sometimes you may want to number the records on your report. You can do this by using a calculated control and the RunningSum property. First, you create a text box and set its ControlSource property to =1.

Then you set the RunningSum property for the text box. If you want the numbering to start over for each group, set the property to Over Group. If you want to accumulate a running sum for the entire report, set the property to Over All.

The expression sets the value of the text box control to 1. Because the RunningSum property is used to accumulate the value, the text box is increased by one for every row. The Sales by Product with Running Sum and Numbered Rows report in the sample database demonstrates this technique. Entering a period (.) in the Format property box appends a period to the end of the number.

Download the sample database

The sample database includes the reports that demonstrate summing with sample data drawn from the Northwind.mdb sample database that is included with Access.

For more information

 
 
Applies to:
Access 2003