Creating TOTALS in Access queries

In Grade 12 one of the most advanced skills you will learn is how to aggregate data using a query. Quite honestly the hardest part about this type of Microsoft Access query seems to be remembering to click the Totals button in the first place! From there it is a matter of translating relatively simple logic into a query.

1. A practical exercise

Let’s consider the following scenario: counting the number of students in the classroom by gender.

Starting at one end of the classroom and keeping a running total for each grouping, male and female etc, will work, but requires the extra overhead of tracking separate totals for each gender while you count.

The simpler solution is to first split the students into groups based on gender and then count the students in each group. The small overhead upfront of grouping the students will pay off almost immediately.

2. Group on 1 field

To do the same thing in Access, create a query based on a suitable table. Let’s assume we have a table with student details that includes an ID field (PK, AutoNumber) and a Gender field.

1. Create a new query
2. Add the ID and Gender fields to the query
3. Click the Totals button on the Design menu
4. In the query design grid, in the ID field (the PK), in the Total row, click where it says “Group By” and select Count
5. In the query design grid, in the Gender field in the Total row, check that “Group By” is selected
6. If you read the query it “says”: group the records by Gender and count the ID’s
7. Save the query as qryLearnersByGender
8. Run the query
9. A distinct set of Gender values will be listed in the Gender column with the count of records for each of those genders in the CountOfGender column

3. Group on 2 fields

This Query groups on 2 fields, mimicking Excel’s COUNTIFS() function.

To perform the same “calculation” in Access to establish how many of each Gender like ice-cream:

1. Create a Query in Design View
2. Add tblResponses
3. Add the responseID field
4. Add the Gender field
5. Add the Q1 field
6. Activate the Totals feature…
7. to add the Total row to the Query
8. Select Count for the responseID field
9. Leave the Gender field on Group By
10. Leave the Q1 field on Group By
11. Add the Criteria to the Q1 field: “Yes”
12. Run the Query!

4. Dig deeper

The query tool in Access is really just a graphic tool for creating SQL query statements. Take a look at the SQL created for the above queries by selecting the SQL View from the View button. The SQL may seem daunting at first, yet it is actually pretty readable (SQL was designed to be human-readable).

SELECT Count(tblLearners.ID) AS CountOfID, tblLearners.Gender
FROM tblLearners
GROUP BY tblLearners.Gender;

SELECT Count(tblReponses.responseID) AS CountOfresponseID, tblReponses.Gender, tblReponses.Q1
FROM tblReponses
GROUP BY tblReponses.Gender, tblReponses.Q1
HAVING (((tblReponses.Q1)="Yes"));