How to sum a column of numbers in a report by page in Access 2002 (296249)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q296249
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Moderate: Requires basic macro, coding, and interoperability skills.

For a Microsoft Access 97 version of this article, see 132017.

SUMMARY

In a Microsoft Access report, you can sum a column of numbers by group or over the entire report, but there is no built-in way to print the sum of a column on each page of a report. This article shows you how you can use code to print the sum of a column of numbers on each page of a report. The code assumes the field that you are summing is a Number field.

MORE INFORMATION

You can print the sum of a column on each page of a report in two ways. You can sum the column of each page, and then add the total to the total of each subsequent page (a running sum). Or you can sum the column of each page separately.

Creating the Report to Be Used in the Examples

  1. Open the sample database Northwind.mdb or sample project NorthwindCS.adp.
  2. Create a new report that is based on the Products table, click AutoReport: Tabular, click the Products table, and then click OK.
  3. On the View menu, click Design View.
  4. Delete the text boxes to the right of the UnitPrice text box. Add another text box to the detail section, and then enter the properties of the text box as follows:
       Name: RunSum
   Control Source: UnitPrice
   Visible: No
   Running Sum: Over All
  5. Save the report as Report1.

Creating a Running Sum for the Entire Report with Subtotals on each Page

  1. Open the Report1 report in Design view.
  2. Add a text box to the page footer section of the report, and then set the ControlSource property of the text box to =runsum.
  3. Preview the report. Note that the running sum is displayed at the bottom of each page.

Summing a Column for Each Page Separately

  1. Open the Report1 report in Design view.
  2. Add a text box to the page footer section of the report, and then set the Name property of the text box to PageSum.
  3. On the View menu, click Code, and then type the following line in the Declarations section of the report module:
    Dim x As Double
  4. Click the arrow in the Object combo box located at the top-left side of the Module window, and then click PageFooterSection. Click the arrow in the Event combo box located at the top-right side of the Module window, and then click Print. Add the following code to the PageFooterSection_Print event procedure.
    Me!PageSum = Me!RunSum - x
x = Me!RunSum
  5. Click the arrow in the Object combo box located at the top-left side of the Module window, and then click ReportHeader. Click the arrow in the Event combo box located at the top-right side of the Module window, and then click Print. Add the following code to the ReportHeader_Print event procedure.
    x = 0
  6. Close the Module window, and then preview the report. Note that the sum for the Unit Price for that page appears on each page of the report.

REFERENCES

For more information about running sums, click Microsoft Access Help on the Help menu, type running sum in a report in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MinorLast Reviewed:9/26/2005
Keywords:kbReport kbhowto kbusage KB296249
©2004 Microsoft Corporation. All rights reserved.