Tables & Queries:
Calculated Values - Totals

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

 The Totals button Button: Totals reveals the Totals row in the Query Design View.  A select query can use one of several aggregate functions in the Totals row to summarize data. ("Aggregate" just means to bring together as a whole.) This "total" does not have to be a sum of numbers. It can be:

  • Total row - list of choicesGroup By - to group the results on each unique value in the selected field

  • Sum - add up the values in this field

  • Avg  - average the values

  • Min  - find the smallest value

  • Max  - find the largest value

  • Count - how many records are the same for this field

  • StDev, Var -Statistical functions that you are not likely to use

  • Total row - list of choicesFirst, Last - to find the first or last record in the datasheet after the query is run. Useful for large datasheets when Min or Max are not appropriate.

  • Expression - when the field is calculated using another field that is an aggregate function

  • Where - for fields that have criteria for restricting which records are included

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing TablesTo subtopics
    Designing Queries Arrow: subtopic open
    Icon: StepSelect Query
    Icon: StepCalculated Values-Text
    Icon: StepCalculated Values-Numbers
    Icon: StepCalculated Values-Totals
    Icon: StepCalculated Values-Dates
    Icon: StepParameter Query
        Action Queries
    Icon: StepMake-Table Query
    Icon: StepAppend Query
    Icon: StepDelete Query
    Icon: StepUpdate Query
    Icon: StepCrosstab Query
    Icon: StepDocument a Query
    ExercisesTo subtopics

Project 4: Forms & Reports


Rules for Creating an Aggregate Query:

  • Include the field that you are "totaling" in the design grid

  • Include the fields you need to group, sort, or filter.

  • Check the box for Show only for the totaled field and for the fields you want to group on.

  • Do not include any other fields.

  • You can not use the * to include all fields.

  • The order of the fields is important for grouping and sorting

Query Design View: Count of test scores by class and letter gradeExample: Aggregate function - Count

(from LanguageArtsClasses.mdb)

Question: How many A's, B's, etc. are there on the spelling test on Unit 24 for each of the three classes?

Answer: Use a query that uses the aggregate function Count. Group records based on each class and each letter grade. I needed 3 tables- Classes/Subjects, Scores, and Assignments/Tests- and 4 fields - Score, Class, LetterGrade, and A/T ID.

Total Row: In the query's Total row I chose Count for the Score field and Group By  for Class and LetterGrade and Where for the A/T ID field.

Criteria: The Criteria row for the Type field contains "5", which is the identification number for the particular test we are looking at. The Show box is automatically not checked, because you picked Where for the Total cell.

Example: Aggregate query - datasheetResults: After running the query, the datasheet shows a CountOfScore value, grouped by class (A, B, D) and then by letter grade (A, B, C, D, F).  (Yes, it is confusing to have named the class sections with letters!)  So we can say that in class A there were 9 A's and 5 B's, etc. In class B there were 4 A's, 4 B's, etc. But what is that first entry for class B - CountOfScore=0 and no letter grade?? There was at least 1 score that was Null! A null score would be a record that exists for a test but the score has not been entered yet.  The Count function won't tell us how many of those there were. Strange!

Null Values: You have to be careful about Null values with functions. Different functions may handle nulls differently. The Count function did not actually count the null scores, but it did report that there was at least 1 in the scores for class B.

Icon: Step-by-Step 

Step-by-Step: Calculated Values - Totals

 Icon: Step-by-Step

What you will learn:

to use the Total row to count records
to open a second database while the first remains open
to use the Total row to sum and to average
to group a total's results on a calculated field

Start with:  Class disk, Projects database open, mytrips.mdb available (created in Project 2: Access Basics)

Calculate Total: Count

You will create a query to answer the question "How many staff members are assigned to each project?" You will need a Total query that uses the Count function to count employees. You can then group the results on ProjectName or ProjectID.

  1. Database Window, showing queriesIf necessary, switch to the Database Window and view the Queries list.

  2. Click the New button Button: New Query (2003) at the top of the Database Window.
    The New Query dialog appears.

  3. Select Design View and click OK.
    The Query Design View appears with the Show Table dialog open.

  4. Query Design View: counting employees in each projectSelect the tables Projects and ProjectStaff and click Add.
     The two tables are added to the upper section of the Query Design View with a join line based on the relationship that you defined between them earlier.

  5. Close the Show Table dialog.

  6. From the table Projects drag the field ProjectName and drop it on the grid at the bottom.

  7. From the table ProjectStaff drag the field EmployeeID and drop it on the grid.

  8. Click the Totals button Button: Totals in the toolbar to show the Totals row.

  9. In the column for ProjectName  select Group By.
    In the columns for EmployeeID select Count.

  10. Query Datasheet View: count of employees by projectIcon: Run Run the query.

  11. Drag the right edge of each column until the column is wide enough to read everything.
    The datasheet shows how many employees are assigned to each project in the database.

  12. Icon: Save Save the query as QCount Project Staff.

  13. Close the query.

Open a Second Database

The Projects database does not yet have any number fields that are suitable for adding up or averaging. The MyTrips database that you created in Project 2 does. Recall that to open a second database without closing the first one requires you to first open a new instance of Access. Various methods were discussed in Project 2, Access Basics: Open Access.'

  1. Task Pane: Getting Started: Open - mytrips.mdbDo not close the Projects database.
    Open a new Access window.
    One way: 
     Start | All Programs or Programs  | Microsoft Office | MS Access.
    A  new Access window appears. The task pane should be open at the right with recently used files showing in the Open list.

  2. If mytrips.mdb is in the list, click on it to open it.
    (Your list will be different from the illustration.)
    If mytrips.mdb is not in the list, click on the link More... Link: More... and navigate to where you saved it at the end of Project 2 of these lessons. Click the file and then the button Open to open it.

    Icon: TroubleProblem: List has more than one mytrips.mdb
    Cause: You have copies in more than one location.
    Hover over each of the entries in turn. A popup tip shows the path to that file. Decide which copy is going to be your "working" copy and which will be your "backup" copy. Don't work in both of them! You can easily find yourself with two very different versions. So confusing!

    Icon: TroubleProblem: Computer slows down with two copies of Access open
    If your computer responds very slowly with a second copy of Access open, you may not have enough memory to handle two copies at once. In that case, close the Projects database.

Calculate Totals: Sum and Average

You will create a new Totals query in the MyTrips database and experiment a bit with it. You will make changes to answer the following questions:

  • What is the total cost of all the trips?
  • What is the average cost of a trip?
  • What is the total cost of trips by each travel agent?
  1. Database Window: My Trips : Trips tableIn the Database Window, view the Tables.
  2. Select the table Trips.
  3. From the toolbar, click on the New Object button and then on Query.
  4. In the New Query dialog with Design View selected, click on OK.
    The Query Design View appears with the Trips table already in the upper section.
  5. Query Design View: Trips - Sum of CostDrag the Cost field down to the grid and drop it.
  6. Click on the Totals button Button: Totals - on to show the Totals row.
  7. In the Totals row for the Cost field, select Sum.
  8. Query Datasheet View: Trips: Sum of CostIcon: Run Run the query.
    The datasheet has only 1 cell, which shows the sum of all the values in the Cost field.
  9. Query Datasheet View: My Trips:  Average of CostIcon: Design Return to Query Design View and change the Totals row to Avg, for average.
  10. Icon: Run Run the query.
    Again there is only one cell showing, with the average of the Cost value for all trips.
  11. Query Design View: Trips: Sum of Cost by agentIcon: Design Return to Query Design View and drag the field AgentID down to the grid. It automatically shows Group By in the Totals row.
  12. Change the Total choice for the Cost field back to Sum.
  13. Icon: Run Run the query.
    Query Datasheet: SumOfCost by AgentThis time you see values for each travel agent and one for trips when no agent was used.
    The AgentID field shows the last name of the agent instead of the ID number because this field is a Lookup field.
  14. Icon: Save Save the query with the name QTotals-Trips by Agent.
  15. Icon: Experiment Experiment with different choices for the Total row and with different fields. What happens when you:
    • Sum on a text field.
    • Group By more than one field.
    • Group By a different field.
    • Drag the * from the table to the grid and do a total.
    • How could you restrict the query to not show a sum when there was no travel agent?
  16. Close the query without saving your changes.

Group Total Results on a Calculated Field

You can combine what you have learned about the Total feature and the IIf conditional expression to create a calculated field that will group your results in a new way. This is starting to get fun! (Or complicated, depending on your point of view!)

First you will create a Totals query that will Sum the values in the Cost field.

Then you will create a calculated field that will group the trips based on whether they were before the year 2000 or not.
Syntax for IIF expression:
IIf(<expression to evaluate>, <what to show if expression is true>, <what to show if expression is false>)

  1. If necessary, open the MyTrips database again.

  2. Query Design View: MyTripsStart a new query based on the table Trips.

  3. Drag the field Cost from the list of fields in the Trips table down to the grid.

  4. Show the Totals row, by clicking the Totals button Button: Totals.

  5. For the Cost column, select Sum for the Total row.

  6. To create a calculated field, type the following into the Field row of the second column,:
    Before2000: IIf([DateStarted]<#1/1/2000#,"Before","2000 and later")
    Be sure to include all of the punctuation, the hash marks (#) around the date, and the double quote marks around the text strings.
    This calculation returns the value "Before" if the DateStarted is before the year 2000 and a "2000 and later" otherwise.
    The Total row shows Group By since that is the default choice.

  7. Query Datasheet View: My TripsIcon: Run Run the query.
    The Cost values are added together for two groups, start dates before 2000 and those in 2000 and later.

  8. Icon: Save Save your query as QTotals-Trips-2000

  9. Close the database MyTrips and then the blank Access window. The Projects database is still open.