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
Aggregate function - Count
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
not checked, because you picked Where for the Total cell.
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.
Step-by-Step: Calculated Values - Totals
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
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
If necessary, switch to the Database Window
and view the Queries list.
Click the New button
at the top of the Database Window.
The New Query dialog appears.
Select Design View and click OK.
The Query Design View appears with the Show Table dialog open.
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
Close the Show Table dialog.
From the table Projects
drag the field ProjectName and drop
it on the grid at the bottom.
From the table ProjectStaff
drag the field EmployeeID and drop it
on the grid.
Click the Totals button
in the toolbar to show the Totals row.
In the column for ProjectName
select Group By.
In the columns for EmployeeID select
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
Save the query as QCount Project Staff.
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:
not close the Projects database.
Open a new Access window.
| or | | A new Access window appears. The task pane should be open at
the right with recently used files showing in the Open list.
- 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...
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.
Problem: 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!
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?
the Database Window, view the Tables.
- Select the table Trips.
- From the toolbar, click on the New Object button and
then on Query.
- In the New Query dialog with Design View selected, click on
The Query Design View appears with the Trips table already in the upper
the Cost field down to the grid and drop it.
- Click on the Totals button
show the Totals row.
- In the Totals row for the Cost field,
The datasheet has only 1 cell, which shows the sum of all the values in
the Cost field.
Return to Query Design View and change the Totals row
to Avg, for average.
- Run the query.
Again there is only one cell showing, with the average of the Cost
value for all trips.
to Query Design View and drag the field
AgentID down to the grid. It automatically shows Group By in the
- Change the Total choice for the Cost field
back to Sum.
- Run the query.
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.
the query with the name QTotals-Trips by Agent.
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?
- 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
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>)
If necessary, open the MyTrips database
a new query based on the table Trips.
Drag the field Cost
from the list of fields in the Trips table down to the grid.
Show the Totals row, by clicking the Totals
For the Cost column, select Sum for the
To create a calculated field, type the following
into the Field row of the second column,:
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.
The Cost values are added together for two groups, start dates before
2000 and those in 2000 and later.
your query as QTotals-Trips-2000
Close the database MyTrips and then the blank Access window. The
Projects database is still open.