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

• Microsoft Access 2000

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

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

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 based on the Products table, click AutoReport: Tabular, select 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 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 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 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.

Summming 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 set the Name property of the text box to PageSum.



3. On the View menu, click Code, and 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 click PageFooterSection. Click the arrow in the Event combo box located at the top-right side of the Module window, and 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 click ReportHeader. Click the arrow in the Event combo box located at the top-right side of the Module window, and click Print. Add the following code to the ReportHeader_Print event procedure.




x = 0 

6. 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, 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.

Additional query words:

Keywords : kbusage kbdta AccCon FmrCdbeh RptTotal
Version : WINDOWS:2.0,2000,7.0,97
Platform : WINDOWS
Issue type : kbhowto