Microsoft KB Archive/129096

= ACC: How to Sum a Calculation in a Report =

Article ID: 129096

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q129096



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



SUMMARY
This article demonstrates three methods that you can use to sum a calculated 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 another aggregate function, you can sum the expression by repeating it in the report or group footer section and taking its sum. If the expression does include another aggregate function, repeating the expression does not work because Microsoft Access cannot sum a sum. Instead, use a running sum or a function to hold the results.

To demonstrate the three methods, this article uses a report based on the Order Details table in the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 1.x and 2.0).

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

To create the report, follow these steps:
 Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0) Create a report based on the Order Details table. Select Design View. (In Microsoft Access 1.x and 2.0, choose Blank Report).  Create an OrderID group by choosing Sorting And Grouping from the View menu and then entering the following information in the Sorting And Grouping window:

     Field/Expression: OrderID Group Header: Yes Group Footer: Yes

NOTE: In Microsoft Access 1.x and 2.0, there is a space in Order ID.  Add a report header and footer section to the report by clicking Report Header/Footer on the View menu. (In Microsoft Access 1.x and 2.0, Report Header/Footer is on the Format menu).

Method 1: Summing Expressions That Do Not Contain Aggregate Functions
To sum a calculation that does not contain an aggregate function, repeat the calculation with the Sum function. To use this method, follow these steps:

  Add a text box control to the report's detail section to calculate the product of two fields. Note that the Name property is the ControlName property in Microsoft Access 1.x. Set the control's properties as follows:

<pre class="fixed_text">     Name: Extended Price ControlSource: =[UnitPrice] * [Quantity]

NOTE: In Microsoft Access 1.x and 2.0 there is a space in [Unit Price]. </li>  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:

<pre class="fixed_text">     Name: Order Total ControlSource: =Sum([UnitPrice] * [Quantity])

</li>  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:

<pre class="fixed_text">     Name: Report Total ControlSource: =Sum([UnitPrice] * [Quantity])

</li> Preview the report to see the sum of the calculation.</li></ol>

Method 2: Summing Expressions That Contain Aggregate Functions
You can use a running sum to calculate totals for expressions that contain aggregate functions or that reference other controls. To use this method, follow these steps.

NOTE: The RunningSum property is not available in Microsoft Access 1.x.

  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.

<pre class="fixed_text">     Name: Freight ControlSource: =IIf(Sum([UnitPrice]*[Quantity])>100,0.05*Sum([Unit _ Price]*[Quantity]),0)

NOTE: In Microsoft Access 1.x and 2.0, there is a space in [Unit Price]. </li>  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.

<pre class="fixed_text">     Name: FreightRunSum ControlSource: =IIf(Sum([UnitPrice]*[Quantity])>100,0.05*Sum([Unit _ Price]*[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. </li>  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:

<pre class="fixed_text">     Name: Freight Total ControlSource: =[FreightRunSum]

NOTE: When you reference another control, use the Val function to convert from a Variant to a number so that the running sum accumulates the value. For more information about running sums not accumulating, please see the following article in the Microsoft Knowledge Base:

115877 ACC2: RunningSum Text Box in Report Not Accumulating Values

</li> Preview the report to see the sum of the calculation.</li></ol>

Method 3: Summing Controls Calculated by Functions
If you use code to calculate a control, you must use a function to calculate its sum. You need the function to hold the results of the calculation across the records. To use this method, follow these steps:

  Create a new module and enter the following code in the Declarations section:

<pre class="fixed_text">     Option Explicit Dim OrderTotal As Double      ' Used for group total. Dim GrandTotal As Double      ' Used for report total.

</li>  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 = tmpAmount End Function </li>  Add the following procedure to initialize the report's variables to 0:

Function InitVars OrderTotal = 0 GrandTotal = 0 End Function </li>  Add the following procedure to retrieve the total for the group:

Function GetGroupTotal GetGroupTotal = OrderTotal ' Reset the variable to 0 for next group. OrderTotal = 0 End Function </li>  Add the following procedure to retrieve the total for the report:

Function GetReportTotal GetReportTotal = GrandTotal End Function </li>  To initialize the variables to 0, set the report's OnOpen property to

<pre class="fixed_text">     OnOpen: =InitVars

and then Add three text box controls to the report's detail section. Note that the Name property is the ControlName property in Microsoft Access 1.x. Set the following properties for the text boxes:

<pre class="fixed_text">     Text box 1: Name: UnitPrice ControlSource: UnitPrice

NOTE: In Microsoft Access 1.x and 2.0, there is a space in Unit Price.

<pre class="fixed_text">     Text box 2: Name: Quantity ControlSource: Quantity

Text box 3: Name: TheProduct ControlSource: =CalcProduct([Report])

</li> <li> Add a text box control to the group footer section to display the total for the group. Set the following properties for the control:

<pre class="fixed_text">     Name: GroupTotal ControlSource: =GetGroupTotal

</li> <li> 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:

<pre class="fixed_text">     Name: ReportTotal ControlSource: =GetReportTotal

</li> <li>Preview the report to see the sum of the calculation.</li></ol>

<div class="references_section">