Jan's Access Tables & Queries: Calculated Values

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.

Results: 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.

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

Start with: , 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.

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.
Select 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.
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 Count.
Run the query.
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.
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: Open Access.'

Do 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.
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!

Problem: 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?

In 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 OK.
The Query Design View appears with the Trips table already in the upper section.
Drag the Cost field down to the grid and drop it.
Click on the Totals button to show the Totals row.
In the Totals row for the Cost field, select Sum.
Run the query.
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.
Return to Query Design View and drag the field AgentID down to the grid. It automatically shows Group By in the Totals row.
Change the Total choice for the Cost field back to Sum.
Run the query.
This 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.
Save 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 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>)

If necessary, open the MyTrips database again.
Start 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 button .
For the Cost column, select Sum for the Total row.
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.
Run the query.
The Cost values are added together for two groups, start dates before 2000 and those in 2000 and later.
Save your query as QTotals-Trips-2000
Close the database MyTrips and then the blank Access window. The Projects database is still open.

Tables & Queries:
Calculated Values - Totals

Rules for Creating an Aggregate Query:

Example: Aggregate function - Count