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.
In this post:
A practical exercise
Let’s consider the following scenario: counting the number of learners 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 learners into groups based on gender and then count the learners in each individual group. The small overhead upfront of grouping the learners will pay off almost immediately.
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 learner details that includes an ID field (PK, AutoNumber) and a Gender field.
- Create a new query
- Add the ID and Gender fields to the query
- Click the Totals button on the Design menu
- In the query design grid, in the ID field (the PK), in the Total row, click where it says “Group By” and select Count
- In the query design grid, in the Gender field in the Total row, check that “Group By” is selected
- If you read the query it “says”: group the records by Gender and count the ID’s
- Save the query as qryLearnersByGender
- Run the query
- 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
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:
- Create a Query in Design View.
- Add tblResponses.
- Add the responseID field.
- Add the Gender field.
- Add the Q1 field.
- Activate the Totals feature…
- too add the Total row to the Query.
- Select Count for the responseID field.
- Leave the Gender field on Group By.
- Leave the Q1 field on Group By.
- Add the Criteria to the Q1 field: “Yes”.
- Run the Query!
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"));