Forms & Reports:
Totals - Simple

Title: Jan's Illustrated Computer Literacy 101


Your report can show a number of different totals. A "total" can be any of the aggregate functions, including Sum, Avg, Max, Min, and Count.

Totals over what?

  • Whole report

  • Group

  • Running sum in each record

It will be important to make it clear what is being totaled!

Print Preview: Report with two groups with totals

Whole report = Grand Total
Groups = Year Total & Episode Total
Running Sum = Cumulative Total with each Detail


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 Values
    Icon: StepSharing Reports
    Special Forms & ReportsTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics


Search  
Glossary
  
Appendix



Totals for Fields

Your report can easily show totals like sums, averages, and counts based on the fields in the report's source. You can total each group in a Group Header or Footer. You can total the whole report in the Report Header or Footer.

How to put a total in a report:

  • Create an unbound text box control in a header or footer.
  • Set the control source for the new control to an expression that calculates the total that you want.

    General Syntax: =Function([FieldName])

    Examples: =Sum([Price])  or  =Avg([Price]) or =Max([Price]) where the field named Price is in the report's source.
    The field that you are totaling is usually shown in the Detail section but does not have to be.

What will be totaled: Group or Report?
It depends on which section you put the new control. If it is in a group header or footer, then the total is over that group only. If it is in the report header or footer, then the total is for the whole report.

TipSpecial Case: Counting records with Count(*)
For counting records, it is almost always better to use the expression =Count(*) instead of an expression based on a field name, like =Count([Price]). The two expressions do not produce exactly the same results. =Count([Price]) will not count records that do not have a value in the Price field, but =Count(*) will! You almost always will want to count ALL records.

TipTotals Per Page? The method above will not work to calculate totals on a page. Such a total cannot be calculated until after the report is formatted for print. So, the page header and footer will not accept an aggregate function like Sum or Avg or Count. It is possible to use Visual Basic code to create page totals: Details from MicrosoftIcon: Off site.


Example: One Group Level with Sum

To sum the price paid for all the items in a group, you can add a new unbound control to the group footer.

Dialog: Sorting and Grouping - one group, one sort Report Design View: one group with a total

Print Preview: group with totalIn the illustration from the Star Wars Collectibles database, the new control is in the group footer. The control source for the new control is =Sum([Amount Paid]) where the field named Amount Paid is in the report's source. The field is usually shown in the Detail section, but does not have to be anywhere in the report for the total to work!

The report shows a total in the footer for each group.
 


Example: Two Group Levels with Sum & Grand Total

When you have two or more group levels, you can do totals for each group level. The report as a whole can also have a total.

TipLabel the totals clearly so you can tell which total goes with which grouping. Staggering the totals across the page usually helps. Formatting different levels differently and/or using divider lines also helps.

Dialog: Sorting and Grouping - two groups, one sort Report Design View: two groups with totals, report total

Print Preview: report with totals for two groups and whole reportThe Sorting and Grouping dialog shows 2 groups and one additional sort. The same calculation goes in each of the three controls for totals
 =Sum([Amount Paid]).

Access figures out which records should be totaled for each subtotal. Group Headers/Footers are so helpful!

The report shows a subtotal for each group of years within each episode, a subtotal for each episode, and then a grand total for the whole report. The order of fields in the dialog is important!


 


Example: Running Sum

A running sum shows a total that increases with each record or group. The example below shows a current total with each record.

Print Preview: Report with running sum in each record

You use the Running Sum property for a control to make it accumulate values. You can accumulate over a group (Over Group) or over the whole report (Over All).

Dialog: Properties - Running Sum Report Design View: with control to collect running sum in Detail section

Print Preview: collecting a running sum in Detail sectionThe illustration shows a running sum for each record in the column Cumulative Total at the right. The control uses the same control source as the control in the Paid column, the field AmountPaid. The two controls have different names but the same source. The total is accumulating over the whole report.
 
 

TipUse Running Sum to number each record:

  • Print Preview: Report with records numberedCreate an unbound text box control in Detail
     
  • Set Control Source to =1.
     
  • Set Running Sum property to Over All to continue numbering in sequence for the whole report, as in the illustration.

    Set Running Sum to Over Group to restart numbering with each group.
     
  • Type a period (.) in the Format property to append a dot to each number, as in the illustration.
     

Icon: Step-by-Step 

Step-by-Step: Totals - Simple

 Icon: Step-by-Step

What you will learn:

to count records in groups and the whole report
to edit the report's source in Query Builder
to manage sorting with the Sorting and Grouping dialog
to create simple total of field values for group and whole report
to create a running sum in the Detail section
to number records with Running Sum

Start with:  Class diskresource files, worldtravel.mdb, report Clients - by country and postal code from the previous lesson, Trips table and QFullName-Staff query imported in previous lesson

World Travel Inc. wants your report to show how many clients are in each country. You need to add totals to your report. For this report the Count(*) function will work well.

After finishing up the client report, you will create another report based on the Trips table and create some simple totals for it.

Count Records: Using Count(*)

The easiest way to count records is to use the special function Count(*).

  1. If necessary, open the report Clients - by country and postal code from the previous lesson.
     

  2. In the Sorting and Grouping dialog, change the PostalCode group to show a footer.
    Country already is showing a footer.
     

  3. Click on the Text Box button Button: Text Boxo n the Toolbox bar.
    The pointer changes to the text box shape Pointer: Text Box.
     

  4. Click in the Country footer at about the 2" mark.
     

  5. Type in the label for the new control: Total for Country:
     

  6. In the new control, type =Count(*)
    (Alternatively: Type in the Control Source property in the Properties dialog)
    Report Design View: controls for Country footer
     
  7. Format the label:
       Font Size = 10
       Bold
       Color = Red
     
  8. Report Design View: Clients-by country and postal code - adding a group totalWith the label selected, click on the Format Painter button Button: Format Painter.
     
  9. Click on the text box in the Country Footer.
    The formatting of the label is applied
     
  10. Resize the label and text box to AutoFit.
     
  11. Arrange the label and text box around the 2" grid line.
     
  12. Print Preview: Clients - by country and postal codesSwitch to Print Preview.
    Each Country group now has a count total.
     
    Icon: ConfusionPoint of Confusion: Alignment
     
    The total's text box in Design View looks like it is aligned to the left. The totals in the report are aligned at the right. The actual total values are numbers, which are aligned by default at the right. In the Design View you see an expression, which is text. Text is aligned by default to the left. Confusing!
     
  13. Switch to Report Design View.
     
  14. Similarly, add a total to the PostalCode footer that counts the number of records in each group.
     
  15. Format this new total to coordinate with the PostalCode header:
       Font Size = 10
       Bold
       Font Color = Green

    Report Design View: Clients with 2 group totals Print Preview: Clients with group totals
     
  16. Switch to Report Design View and add a new text box to the Report Footer.
     
  17. Print Preview: Clients - country totalsEdit the label to read:
      Total of Clients:
     
  18. Edit the text box source to read:
      =Count(*)
     
  19. Format the control and its label to coordinate with the report header as:
       Font Size = 12
       Bold
       Font Color = Black

     
  20. Switch to Print Preview and navigate to the last page.
    The report footer with the report total appears at the end of the report.
     
  21. Make any necessary corrections.
     
  22. Icon: Save Save the report as Clients - country totals.
     
  23. Icon: Print Print the last page only.
     
  24. Close the report.

Start a Report with the Wizard

The Clients table has no number fields to use for doing numerical calculations. We must change to a different table to learn how to do that in reports. The Trips table is suitable. You imported this table along with some others and some queries from another database in an earlier lesson.

  1. Start the Report Wizard.
     
  2. From the Trips table include:
      TripID, ClientID, AgentID, DiscountID, Price
     
  3. From the table Discounts include:
      Discount %
     
  4. From the table Clients include:
      LastName, FirstName, MiddleName
     
  5. From the query QFullName-Clients (imported in an earlier lesson) include:
      CFullName
     
  6. From the query QFullName-Staff (imported in an earlier lesson) include:
      SFullName
     
  7. Choose to group on SFullName.
     
  8. Sort on LastName, FirstName, MiddleName, which are the client's name.
     
  9. Choose Stepped layout in Portrait orientation.
     
  10. Choose Corporate style.
     
  11. Name the report Trips by Agent.
     
  12. Print PreviewInspect the report.
    This is a start, but it needs a lot of work, like most wizard reports.

    Problems:  
    Some of the column labels are cut off.

    The trips are grouped by the agent, but the control for the agent's name is too narrow.
     
    You don't need both the client's full name and the individual name fields.
     

Customize Report

  1. Switch to Report Design View.
     
  2. Report Design View: adjustedDelete the label and control for:
    LastName, FirstName, MiddleName, ClientID, AgentID, DiscountID, Discount %
     
  3. Edit the SFullName column label to read Agent.
    Edit the CFullName column label to read Client.
     
  4. Move the CFullName control and its matching column label to the position that LastName had before you deleted it.
     
  5. Print Preview: adjustedEnlarge the controls for the agent and client names to at least 1.5" wide.
     
  6. Move the TripID and Price controls and their column labels over to the left next to the Client control and column label.
     
  7. Switch to Print Preview.
     
  8. Inspect.
    Better.
     
     
    The agents are not alphabetized correctly. The query QFullName-Staff did alphabetize these names, but the report is not picking it up. The client names would have the same problem but we included the original name parts in the wizard and sorted on them. Confusing!
     
    Did you notice? The report title shows fine in the preview, but in the Design View it looks cut off.

Revise Report's Source in Query Builder

The Report Wizard created a query that is the report's source. You can edit that query yourself in the Query Builder window. Let's try to fix the sorting of the agents by changing the query. There will be a glitch! Do not panic. Keep reading. All will be explained.

  1. Switch to Report Design View.
     
  2. Click on the upper left corner of the report window to select the whole report.
    Report Design View: whole report selected. Properties dialog - ellipsis button
  3. In the Properties dialog, click in the Record Source box and then on the ellipsis button Button: Ellipsis (3 dots) that appears.
    The Query Builder window appears. It shows the tables and queries and the fields chosen for the query that is the control source for the report.
     
    Report Design View: green error triangles. Info menu: no such field in field listIcon: Trouble Problem: Changed record source unintentionally
    It is easy to click on the arrow instead of the ellipsis button. That action drops a list of tables and queries that you can choose as the source. If you select one of those, the current query is lost. Controls that are based on fields that are no longer available will show a green error triangle. You can use Undo to get it back, but not if you have done too many other things in the meantime.
     
  4. Delete the Discounts table, since we are no longer using those fields.
    Query Builder
  5. From the QFullName-Staff list, drag the fields LastName, FirstName, and MiddleName to the grid.
    (Be sure that the Table row shows the correct source. The Clients table also has fields with these names!)
     
  6. Query Builder: name part fields included for sortingSort these new columns Ascending.
     
  7. Click the close button on the toolbar. When prompted, save the SQL statement.
     

    You are back in Report Design View.

    WarningIf you click the Save button on the toolbar, you are prompted to save your design as a separate query. Not what we need!
     

  8. Switch to Print Preview.
    Glitch! A message box appears. There is some confusion about the two LastName fields in your query, even though we had to pick which table they came from. <Grrrr!>
    Message: The specified field 'LastName' could refer to more than one table listed in the FROM clasue of your SQL statement.
     
  9. Click on OK. The report fails to open!
     
  10. Open the Query Builder again.
    You can fix this glitch by assigning new names.
     
  11. Rename the name columns for Staff names by replacing as follows:
    SLast: LastName
    SFirst: FirstName
    SMiddle: MiddleName

     
  12. Close the Query Builder and save your changes.
     
  13. Print Preview: Trips by Agent, after changing record sourceSwitch to Print Preview.
    It opens. That part is better!
    You have learned how to revise a report's source.
    But the report still does not sort the agent's name properly. Humph!

    The problem is not with the query but with the Sorting and Grouping dialog.
     

Manage Sorting

The Sorting and Grouping dialog is the primary controller for sorting in a report. It is over-riding what you did in the record source query.

You must include in the report's source all of the fields that you want to use  in the Sorting and Grouping dialog. The sorting in the original source table or query may not be what you see in the report.

  1. Dialog: Sorting and Grouping - with rows for name partsIf necessary, open the Sorting and Grouping dialog.
     
  2. Inspect the rows.
    The group SFullName is being sorted ascending, but that field holds a complete name, starting with the first name. Aha! So the Agents ARE in alphabetical order, but starting with the first name. That's not the alphabetical order that we wanted!
     
    You can fix that, now that you have included the fields that make up the full name in the report's control source.
    (See. You did not waste that effort above!)
     
  3. Add rows for SLast, SFirst, and SMiddle, sorted Ascending.
     
  4. Switch to Print Preview.
    No change. Hmmm. Why not? This time it is the order of the sorting that is the problem.
     
  5. Switch to Report Design View.
     
  6. Dialog: Sorting and Grouping: dragged rows upIn the Sorting and Grouping dialog, drag the staff name rows that you just added up above the SFullName row.
     
    The dialog still shows that SFullName is sorted. But, the values are being sorted within a grouping on SLast/SFirst/SMiddle - which only has 1 member. So there is no effect.
     
  7. Print Preview: after more adjustingSwitch to Print Preview.
    Success! The agents are now in alphabetical order.
     
    Now that the groupings are straightened out, we get do some work with totals, which was the point of this lesson!
     
  8. Icon: Save Save the report as Trips by Agent-sorted.
     

Create Simple Total

You can easily do totals for values directly from fields. The next lesson will work on how to do totals with calculated controls.

  1. In the Sorting and Grouping dialog, show the footer for the group SFullName.
     
  2. Create in the footer a new unbound text box control using the Text Box tool.
     
  3. Change the label to read Agent Total:
     
  4. In the Properties dialog for the new control,  change:
    • Name to TotalPrice.
    • Control Source to =Sum([Price])
    • Format to Currency
       
  5. Copy the TotalPrice control
     
  6. Click on the Report Footer bar to select the section.
     
  7.  Paste.
    The control is pasted into the Report Footer, which automatically enlarges.
     
  8. Change the name of the control in the Report Footer to GrandTotalPrice.
    Report Design View: Trips by Agent, with simple totals for Price
  9. Switch to Print Preview.
    The report includes a total for each grouping and for the whole report.

    Print Preview: Trips by Agent, with totals for Price

  10. Icon: Save Save the report as Trips by Agent - Total Price.

Create a Running Sum

While it is not particularly interesting for this report, let's practice creating a running sum in the Detail section. This method can be used for situations where you want to total a calculated control (as we will do in the next lesson).

  1. Switch to Report Design View.
     
  2. In the Detail section, create a new unbound text box to the right of the Price control.
     
  3. Use the key combo CTRL + X to delete the control's label.
     
  4. Click in the Page Header and paste. The deleted label appears at the upper left.
     
  5. Drag the pasted label to the right to be a column label above the new text box.
     
    TipWhere it pastes: Objects pasted in Design View appear at the upper left of the section that was selected when you pasted. They can be hard to see if they overlap other objects or are small, like thin lines.
     
  6. If necessary, align the new label with the other labels in the Page Header section.
     
  7. Edit the label text to read Cumulative Total
     
  8. Dialog: Properties - CumTotalIn the Properties dialog,
    • Name the new control in the Detail section CumTotal.
    • Set the control source for the new control to =[Price].
    • Set Format to Currency.
    • Set the Running Sum property to Over Group.
       
  9. Add a label to the center of the Page Footer containing your name.
     

      Report Design View: Trips by Agent, with Cumulative Total
     

  10. Print Preview: with cumulative totalSwitch to  Print Preview.

    The new column shows a total that increases with each new record in the group. The last record in a group will have the same total as the group's total, which was calculated separately.

    In this report the column resets to zero for each new group. If the Running Sum property for the control is set to Over All, then the total will not reset but will keeping accumulating for the whole report .
     

  11. Icon: Save Save the report as Trips by Agent - Cumulative Price
     

Number Each Record

The Running Sum property can be used to number each record, either within a group or over the whole report.

  1. Report Design View: Trips by Agent, with numbered recordsSwitch to Report Design View.
     
  2. Add a new unbound text box control to the Detail view, at the left for the client name.
     
  3. Delete the label for the new control.
     
  4. Align the new control with the top of the other Detail controls.
     
  5. In the Properties dialog for the new control, change the following:
    • Control Source to =1
    • Format  - type a period
    • Running Sum to Over All
       
  6. Print Preview: Trips by Agent, with numbered recordsIcon: Save Save the report as Trips by Agent - Numbered
     
  7. Icon: Print Print. (1 page)