You are currently offline, waiting for your internet to reconnect

ACC2000: How to Sum a Calculation in a Report

This article was previously published under Q208850
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY
This article demonstrates three methods that you can use to sum acalculated control in a report. Because the Sum() function, as well as the other aggregate (totals) functions can reference only a field and not a control, you must use one of these methods if you want to sum a calculated control.
MORE INFORMATION
If the expression in the calculated control does not include anotheraggregate function, you can sum the expression by repeating it in thereport or group footer section and taking its sum. If the expression doesinclude another aggregate function, repeating the expression does not workbecause Microsoft Access cannot sum a sum. Instead, use a running sum or afunction to hold the results.

To demonstrate the three methods, this article uses a report based on theOrder Details table in the sample database Northwind.mdb.

NOTE: For calculating totals in a page footer section, use only methods two and three.

Creating the Report

To create the report, follow these steps:
  1. Open the sample database Northwind.mdb.
  2. Create a report based on the Order Details table in Design View.
  3. Create an OrderID group by clicking Sorting And Grouping on the View menu, and then entering the following information in the Sorting And Grouping dialog box:
       Field/Expression: OrderID   Group Header: Yes   Group Footer: Yes					
  4. Add a report header and footer section to the report by clicking Report Header/Footer on the View menu.

Method 1: Summing Expressions That Do Not Contain Aggregate Functions

To sum a calculation that does not contain an aggregate function, repeatthe calculation with the Sum() function. To use this method, followthese steps:
  1. Add a text box control to the report's detail section to calculate the product of two fields. Set the control's properties as follows:
       Name: Extended Price   ControlSource: =[UnitPrice] * [Quantity]					
  2. Add a text box control in the OrderID group footer section to calculate the total for the group. Set the following properties for the control:
       Name: Order Total   ControlSource: =Sum([UnitPrice] * [Quantity])					
  3. Add a text box control in the report's footer section to calculate the total for the report. Set the following properties for the control:
       Name: Report Total   ControlSource: =Sum([UnitPrice] * [Quantity])					
  4. Preview the report to see the sum of the calculation.

Method 2: Summing Expressions That Contain Aggregate Functions

You can use a running sum to calculate totals for expressions thatcontain aggregate functions or that reference other controls. To usethis method, follow these steps:
  1. Add a text box control to the report's OrderID group footer section to calculate a control that charges freight only when the order is over $100. Set the following properties for the control.

    NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example.
       Name: Freight   ControlSource: =IIf(Sum([UnitPrice]*[Quantity])>100,0.05* _                     Sum([UnitPrice]*[Quantity]),0)					
  2. Add a second text box control to the report's Order ID group footer section to calculate the running sum for this control. Set the following properties for the control.

    NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example.
       Name: FreightRunSum   ControlSource: =IIf(Sum([UnitPrice]*[Quantity])>100,0.05* _                  Sum([UnitPrice]*[Quantity]),0)   RunningSum: Over Group   Visible: No						
    NOTE: When you test a running sum, set the Visible property to Yes to verify that the control is accumulating. Once you verify that it does, hide the control by setting the Visible property to No.
  3. Add a text box control to the report's footer section to display the total by referencing the RunningSum property. Set the following properties for the control:
       Name: Freight Total   ControlSource: =[FreightRunSum]					
  4. Preview the report to see the sum of the calculation.

Method 3: Summing Controls Calculated by Functions

If you use code to calculate a control, you must use a function tocalculate its sum. You need the function to hold the results of thecalculation across the records. To use this method, follow these steps:

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements.
  1. Create a new module and enter the following code in the Declarations section:
    Option ExplicitDim OrderTotal As Double       ' Used for group total.Dim GrandTotal As Double       ' Used for report total.					
  2. Add the following procedure to calculate the product of two fields:
    Function CalcProduct (R as Report)   Dim tmpAmount As Double   tmpAmount = R![UnitPrice] * R![Quantity]   ' Remember the total for the group.   OrderTotal = OrderTotal + tmpAmount   ' Remember the total for the report.   GrandTotal = GrandTotal + tmpAmount   CalcProduct = tmpAmountEnd Function					
  3. Add the following procedure to initialize the report's variables to 0:
    Function InitVars ()   OrderTotal = 0   GrandTotal = 0End Function					
  4. Add the following procedure to retrieve the total for the group:
    Function GetGroupTotal ()   GetGroupTotal = OrderTotal   ' Reset the variable to 0 for next group.   OrderTotal = 0End Function					
  5. Add the following procedure to retrieve the total for the report:
    Function GetReportTotal ()   GetReportTotal = GrandTotalEnd Function					
  6. To initialize the variables to 0, set the report's OnOpen property as follows:
          OnOpen: =InitVars()						
    and then add three text box controls to the report's detail section. Set the following properties for the text boxes:
       Text box 1:      Name: UnitPrice      ControlSource: UnitPrice   Text box 2:      Name: Quantity      ControlSource: Quantity   Text box 3:      Name: TheProduct      ControlSource: =CalcProduct([Report])					
  7. Add a text box control to the group footer section to display the total for the group. Set the following properties for the control:
       Name: GroupTotal   ControlSource: =GetGroupTotal()					
  8. Add a text box control to the report's footer section to display the total for the report. Set the following properties for the control:
       Name: ReportTotal   ControlSource: =GetReportTotal()					
  9. Preview the report to see the sum of the calculation.
REFERENCES
For more information about calculating totals in a report, click Microsoft Access Help on the Help menu, type calculate a total or average for a group of or all records in a records in a report in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
inf
Properties

Article ID: 208850 - Last Review: 06/23/2005 23:20:00 - Revision: 3.0

  • Microsoft Access 2000 Standard Edition
  • kbhowto KB208850
Feedback