ACC: How to Sum a Column of Numbers in a Report by Page

This article was previously published under Q132017
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY
In a Microsoft Access report, you can sum a column of numbers by group orover the entire report, but there is no built-in way to print the sum of acolumn on each page of a report. This article shows you how you can usecode to print the sum of a column of numbers on each page of a report. Thecode assumes the field that you are summing is numeric.

This article assumes that you are familiar with Visual Basic forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout Visual Basic for Applications, please refer to your version of the"Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in MicrosoftAccess version 2.0. For more information about Access Basic, please referto the "Building Applications" manual.

NOTE: This article explains a technique demonstrated in the samplefiles, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0)and RptSmp97.exe (for Microsoft Access 97). For information about howto obtain these sample files, please see the following articles in theMicrosoft Knowledge Base:
145777 Microsoft Access 95 Sample Reports Available in Download Center

175072 Microsoft Access 97 Sample Reports Available in Download Center
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 totalof each subsequent page (a running sum). Or you can sum the column of eachpage separately.

Creating the Report to Be Used in the Examples

In Microsoft Access 7.0 and 97
  1. Open the sample database Northwind.mdb.
  2. Create a new report based on the Products table, click AutoReport: Tabular, select the Products table, and then click OK.
  3. Save the Report as Report1 and close Print Preview.
  4. Delete the text boxes to the right of the UnitPrice text box. Add another text box to the detail section, and enter the properties of the text box as follows:
    Name: Runsum
    Control Source: UnitPrice
    Visible: No
    Running Sum: Over All
In Microsoft Access version 2.0:
  1. Open the sample database NWIND.MDB.
  2. Create a new report based on the Products table, and then click Report Wizards.
  3. In the "Which Wizard do you want?" box, click Tabular, and then click OK.
  4. Add the Product ID and Unit Price fields from the Available Fields box to the Field Order On Report box, and then click Finish.
  5. Save the report as Report1 and close Print Preview.

    Note that the Report Wizard added a text box bound to the Unit Price field in the detail section.
  6. Add another text box to the detail section and set the properties ofthe text box as follows:
    Name: Runsum
    Control Source: Unit Price
    Visible: No
    Running Sum: Over All

Adding the Total to the Total of Each Subsequent Page (a Running Sum)

To add the total to the total of each subsequent page in Microsoft Access2.0, 7.0, and 97, follow these steps:
  1. Add a text box to the report's page footer section and set the text box's ControlSource property to =runsum.
  2. Preview the report. Note that the running sum is displayed at the bottom of each page.

Summming the Column of Each Page Separately

To sum the column of each page separately in Microsoft Access 2.0, 7.0, and97, follow these steps:
  1. Add a text box to the report's page footer section, and set the text box's Name property to Pagesum.
  2. On the View menu, click Code, and type the following line in the Declarations section of the report module:
    Dim x As Double
  3. Click the arrow to the right of the Object box, and click PageFooter (Pagefooter2 in version 2.0). Click the arrow to the right of the Proc box, select Print, and then type the following code:
    pagesum = runsum - x
    x = runsum
  4. Click the arrow to the right of the Object box and click ReportHeader (ReportHeader3 in version 2.0). Click the arrow to the right of the Proc box, select Print, and then type the following code:
    x = 0
  5. Close the Module window and 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 search the Help Index for "Runningsum," or ask the Microsoft Access 97 Office Assistant.
Properties

Article ID: 132017 - Last Review: 12/04/2015 11:34:30 - Revision: 2.3

Microsoft Access 2.0 Standard Edition, Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kbhowto kbprogramming kbusage KB132017
Feedback