The information in this article applies to:
- Microsoft Access versions 2.0, 7.0, 97
---------------------------------------------------------------------
SUMMARY
=======
Moderate: Requires basic macro, coding, and interoperability skills.
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 demonstrates how you can use
code to print the sum of a column of numbers on each page of a report.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about 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 Microsoft
Access version 2.0. For more information about Access Basic, please refer
to the "Building Applications" manual.
NOTE: This article explains a technique demonstrated in the sample file,
RptSampl.exe. For information about how to obtain this sample file, please
see the following article in the Microsoft Knowledge Base:
Article ID: Q145777
Title : INF: Microsoft Access Sample Reports Available on MSL (7.0)
This file is a Microsoft Access 7.0 database, which you can open in
Microsoft Access 97 (but do not need to convert to Microsoft Access 97).
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. For both approaches, first follow these steps.
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 text box's
properties 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 text box's
properties as follows:
Name: Runsum
Control Source: Unit Price
Visible: No
Running Sum: Over All
In Microsoft Access 2.0, 7.0, and 97:
To add the total to the total of each subsequent page, follow these steps:
1. Add a text box to the report's page footer section and set the text
box's Control Source property to =runsum.
2. Preview the report. Note that the running sum is displayed at the bottom
of each page.
To sum the column of each page separately, 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 "Running
sum," or ask the Microsoft Access 97 Office Assistant.
Copyright (c) Microsoft Corporation. All rights reserved.
