Forms & Reports:
Totals - Calculated Controls

 Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

You need a different technique to create totals of values from calculated controls. If you try the simple method from the previous lesson, Access pops up a parameter dialog asking for a value for the calculated control that you are trying to total. This kind of glitch can drive you nuts when you don't know where the problem is coming from!

Print Preview: Calculated Totals, page 1 Print Preview: Calculated Totals, page 2

Two page report, including simple totals
and totals for calculated controls (See below)


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports Arrow: subtopic open
    Import/Export/LinkTo subtopics
    Designing FormsTo subtopics
    Designing Reports Arrow: subtopic open
    Icon: StepReport Wizard
         Printing Reports
    Icon: StepCreate Report Manually
    Icon: StepGroups
    Icon: StepTotals - Simple
    Icon: StepTotals - Calculated
    Icon: StepSharing Reports
    Special Forms & ReportsTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics

Search  
Glossary  
Appendix

Totals for Calculated Values

You cannot use the simple method from the previous lesson for totaling values that come from a calculated control. There are ways, however, to get around this limitation!

Method 1: Repeat the calculation in the control source for the total
(Can not use an aggregate function in the calculation)

  • You have a calculated control in Detail section:
    The control source is an expression that calculates the control's value. The calculation does NOT use an aggregate function (Sum, Avg, Count, Max, Min, etc.).

    In the example report, the control Savings is in the Detail section and is calculated with the expression =[Retail Price]-[Actual Price]
     
  • To hold the total, create an unbound text box control in a Header or Footer:
    As control source, use an expression that performs the total and repeats the calculation instead of using the name of the calculated control, like
    =Sum([Retail Price]-[Actual Price])      instead of =Sum([Savings])
    In the example this expression is used for three controls, two in Group Footers and one in the Report Footer.

    Report Design View: Calculated control and Sum of calculated control Print Preview: report with totals based on a calculated value

    Other aggregate expressions would also work, like
    =Max([Retail Price]-[Actual Price])      or
    =Avg([Retail Price]-[Actual Price]) 

    Error: Parameter dialog appears
    Dialog: ParameterThe expression =Sum([Savings]) seems logical to total the Savings values. This syntax worked to total the fields Retail Price and Actual Cost. However, if you try it with Savings, a parameter dialog will ask you to enter a value for Savings. Since Savings was a calculated control, Access cannot find its value to put in a header or footer.

Method 2: Calculated field in query

  • Make the report's source a query which includes a calculated field.
     
    In the example you could add a column to the source query like:
    Difference:[Retail Price]-[Actual Price]
     
  • Create a control in a header or footer for the total based on the calculated field.
    Like =Sum([Difference]) or =Max([Difference])
     

Method 3: SUM by collecting a running sum
(Can use an aggregate function in the calculated control)

This method is required when the calculation includes an aggregate function (Sum, Avg, Max, Min, etc.) but will also work for simpler calculations. A Running Sum adds up the values. (Duh!) There is no way to do a running average, or any other aggregate function. Just adding up the calculated values.

  • Create a calculated control in a Detail, Group Header, or Group Footer section:
    The control source can use a field name or an expression to calculate the control's value.
    The expression is allowed to use an aggregate function.
    Examples:
    =[Savings]
    =[Retail Price]-[Actual Price]
    =Sum([Retail Price]-[Actual Price])
    =Avg([Retail Price])
    =Max([Retail Price])
    =Max([Retail Price]-[Actual Price])
     
  • Set the Running Sum property for the control to Over Group or Over All.
    Now the control will show an accumulating total.
     
  • For Grand Total: If the Running Sum is set to Over All, then you can repeat the final total in the Report Footer or Header.
    • Create an unbound text box control in the Report Header or Footer.
    • Set its control source to be the name of the control accumulating the running sum.
      Example:
      If the control RunSumSavings is collecting a running sum, then the control in the Report Footer would have =[RunSumSavingsl ] as its control source. This  control will show the last value of the running sum.

    TipTo see both Running Sum and current value:
    Use two controls. One with Running Sum = No to show the record's value and one with Running Sum = Over Group or Over All.

    TipInvisible control: If all you want to see is a grand total for the group or report, you don't need to see the accumulating values for each record. Set the Visible property for the control in the Detail section to No.

Method 4: Create a function - If you used code to calculate a control's value, you must create your own function to get a total.
Example from MicrosoftIcon: Off site

Icon: TroubleProblem: Totals are not accumulating as expected

  • Check the Table Design View to see if your numbers are actually Text Data Type instead of Number or Currency type.
  • Check the order of your choices in the Sorting and Grouping dialog.
  • Check that you are totaling the correct control. (Spelling counts!)
Icon: Step-by-Step 

Step-by-Step: Totals with Calculated Values

  Icon: Step-by-Step

What you will learn:

 to revise report's SQL using Query Builder
to paste a control's label to different section
to create a calculated control
to create a total of calculated values for group and whole report with:
     Method 1: Repeating calculation in Total control
     Method 2: Query with calculated field
     Method 3: Running Sum property

Start with:  Class disk,  resource files, worldtravel.mdb, report Trips by Agent - Total Price from the previous lesson

Revise Report Source, again

The Price shown in the latest version of the Trips by Agent report is the "before discount" price. You need to include in your report the discount percentage and the "after discount" price. Then you can create some helpful totals.

The report's source does not include the value you need for the discount. You must edit the source query again.

  1. If necessary, open the report Trips by Agent - Total Price that you created in the previous lesson.
     
  2. Select the whole report.
     
  3. In the Properties dialog, open the Query Builder for the control source of the report.
     
  4. Add the table Discounts to the query.
    (Click the Add Table button to open the dialog and select Discounts.)
     
  5. Add the field Discount % to the grid.
    (Drag from the Discounts table list and drop on the grid)
     Query Builder: adding Discount % to report's source
  6. Close the query and save the SQL statement when prompted.
    Do not save the SQL statement as a separate query.

Add Control; Separate Label

Now you can add the Discount % field to the report and create a new column.

  1. If necessary, show the Field List by clicking its button Button: Field List on the toolbar.
     
  2. Report Design View: Add Discount % controlDrag the field Discount % from the Field List and drop to the right of the Price field in the Detail section.
     
  3. Click on the new label and try to drag it up to the Page Header.
    You cannot drag it out of the section.
     
    It would be nice to move this formatted label to the Page Header with the other column labels instead of deleting it and creating a new label. There is a way!
     
  4. With the label still selected, use the key combo CTRL + X to delete the label.
    The label vanishes.
     
  5. Report Design View: Pasted label is at upper right, overlapping existing controlsClick in the Page Header section and paste.
    The label appears, but at the far left, not where you clicked. It is hard to see because it overlaps another label.

     
  6. Report Design View: Discount % label dragged to new positionDrag the label to the right of the Price label and above the Discount % control in the Detail section.

     
  7. If necessary, align the new label with the other labels in the Page Header section.
    Align the Discount % control with the others in the Detail section.
     
  8. Print Preview: Discounts out of lineSwitch to Print Preview.
    Whoops. The Discount % values are aligned to the right. The column of values is not under the label.
    You can resize the control, since the numbers are going to be small.
     
  9. Switch to Report Design View.
     
  10. Resize the Discount % control to about 1/4 inch.
    Report Design View: Discout % control resized
     
  11. Print Preview: Discount % values lined up under labelSwitch to Print Preview.
    Better.
    If necessary, change the size or position of the control to line the values up neatly under the label.
     

Add Calculated Control and Label

Working with calculated values is a bit trickier. You will calculate the actual price after discount so that you can add those prices up.

  1. If necessary, open the report Trips by Agent - Total Price in Report Design View.
     
  2. Report Design View: new unbound controlIn the Detail section, use the Text Box tool to create a new unbound control to the right of the Discount % control.
     
  3. Delete the attached label for the new control.
     
  4. In the Properties dialog for the new control, type the expression
    =[Price]-([Price]*0.01*[Discount %])

    Explaining the expression: The numbers in Discount % are numbers like 5 or 10, not in percentage format. So we must multiple the Discount % by 0.01 to get the correct decimal value. The expression calculates the discount amount with the expression [Price]*0.01*[Discount %] and then subtracts it from the Price.
     
  5. Print Preview: Trips by Agent, with Net Price columnIn the Properties dialog, choose Currency for the Format property.
     
  6. Change the control's name to NetPrice.
     
  7. Use the Label tool to create a new label in the Page Header with the text Net Price.
     
  8. Position the new label neatly over the new control.
     
  9. Align the label to the other labels in the Page Header section and the new control to the others in the Detail section.
     
  10. Switch to Print Preview.
    If necessary, return to design view and make corrections.
     
  11. Icon: Save Save the report as Trips by Agent - Net Price.
     

Total of Calculated Values: Using Field Name (Wrong!)

You are now going to create a total for each agent and for the whole report of the Net Price values. There is a problem with using an aggregate function like SUM with calculated values.

  1. Switch to Report Design View.
     
  2. In the SFullName footer section, create a new unbound text box control.
     
  3. Edit the label to read: Net Price Total:
     
  4. Report Design View: Trips by Agent, with Net Price totals, =Sum([NetPrice])In the Properties dialog for the new control:
    • Edit the control source to be: =Sum([NetPrice])
      similar to what you did for the total for Price.
    • Change the name of the control to AgentTotalNet.
       
    • Set the Format property to Currency.
       
  5. Copy the new control and paste it into the Report Footer.
     
  6. Edit the label in the Report Footer to read: Grand Total Net:
     
  7. In the Properties dialog for the new control change the name of the new control to GrandTotalNet.
     
  8. Dialog: Parameter - Net PriceSwitch to Print Preview.
     
    Problem!     A parameter dialog appears, asking for a value for NetPrice.
     
    You cannot use an aggregate function like SUM with a calculated control like NetPrice. Access is not smart enough! In the totals control, you must repeat the calculation that created NetPrice to begin with.
     
  9. Click on Cancel to close the parameter dialog.
     

Method 1: Total of Calculated Values - Repeat Expression

You have now seen what happens when you try the "easy" but wrong way to get a total. Let's do this correctly now.

  1. Edit the control source of the AgentTotalNet and GrandTotalNet controls to read:
    =Sum([Price]-([Price]*0.01*[Discount %]))

    Report Design View: correct expressions for NetPriceTotal and GrandTotalNetSuggestion: Copy the expression (carefully!) from the NetPrice control and paste it into the Control Source property.

    This expression uses the aggregate function Sum with the expression that calculated the Net Price control to start with. (If it weren't for copy and paste, this would be a pain to retype!)
     
  2. Print Preview: Trips by Agents, with totals for Net PriceSwitch to Print Preview.
     
    Success!
     
  3. Icon: Save Save the report. (Trips by Agent - Net Price)
     

Method 2: Total of Calculated Values with Query

Doing calculations in the report's query is usually faster than doing them as the report is formatted. The more records there are, the more important it is to consider such speed issues.

You will now revise the report to calculate the NetPrice in the source query.

  1. Switch to Report Design View.
     
  2. Icon: Save Save the report as Trips by Agent - NetPrice Query.
     
  3. Select the whole report.
     
  4. In the Record Source in the report's Properties dialog, open the  Query Builder.
    (Click in the property box and then click on the ellipsis button.)
     
  5. Insert a new column. ( Insert | Column )
     
  6. Type the definition for a new field in the top row of the new column:
    NetPrice:[Price]-([Price]*0.01*[Discount %])
     Query Builder for SQL statement, with new calculated field
  7. Query Datasheet: Net PriceRun the query to be sure it works.
     
    The datasheet looks good.

    But wait. There are 10 trips in this datasheet. The Trips table has 11 records! What happened?
     
    Missing Records: The joins between tables for this query require that there be values on both sides of the join. This query does not show Trip #11 because that trip does not have a ClientID entered in the Trips table. That may or may not be what you want to happen. There are two easy fixes, depending on what you want to see. You can change the join to show the trip anyway, or (better) you can open the Trips table and enter a ClientID. With the Query Builder window open, you cannot open another Access object to edit the data. We will get back to this shortly, once the report has been changed.
     
  8. Close the Query Builder window and save the changes when prompted.
    Message: Do you want to save the changes made to the SQL statement and update the property?
     
  9. Report Design View: after revised to use query fieldEdit the control NetPrice in the Detail section to use the new field NetPrice as its control source.
     
  10.  Edit  the two totals that used the calculation to use the simple expression =Sum([NetPrice])
       AgentTotalNet
       GrandTotalNet
     
  11. Print Preview: Report after creating new calculated field in source querySwitch to Print Preview.
    Success! The report shows the same numbers as before! With so few records, you certainly would not notice a difference in the time to format the report, but with many records, using the query would make a difference.
     
  12. Icon: Save Save the report. (Trips by Agent - NetPrice Query)
     
  13. Switch to the Database Window.
     
  14. Open the table Trips in datasheet view..
     
  15. Change the ClientID for record 11 from 0 to 1.
     
  16. Close the table.
     
  17. Switch back to the report and open Print Preview again.
    Now the report shows all 11 records with updated totals.

Print Preview: after editing ClientID

Method 3: Total of Calculated Value (with Running Sum)

There are times when you would like to see the total as it accumulates and not just a final total. That is done using the Running Sum property.

  1. Switch to Report Design View.
     
  2. Change the column label Net Price to Cumulative Total
     
  3. Delete the Discount % label in the Page Header and its control in the Detail section.
     
  4. Change the name of the control NetPrice in the Detail section to CumTotal.
     
  5. Change the Running Sum property for CumTotal to Over Group.
     
  6. Add a label to the Page Footer containing your name.
    Report Design View: Trips by Agent, with cumulative total
  7. Switch to Print Preview.
    The total accumulates in each group.
     
  8. Icon: Save Save the report as Trips by Agent - Cumulative Total
     
  9. Icon: Print Print. (1 page)

Print Preview: Trips by Agent, with cumulative totals Print Preview: Trips by Agent - Cumulative Total