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.
Grouping and summarizing data in this way is called aggregating.
Let’s consider the following scenario: counting the number of female learners and male learners in the classroom.
Starting at one end of the classroom and keeping a running total for each grouping, male and female, will work, but requires the extra overhead of tracking separate totals while you count.
The simple solution is to first split the learners into 2 groups by gender and then count the individual groups. The small overhead up front of grouping the learners will pay off soon enough.
To do this 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
The query tool in Access is really just a graphic tool for creating SQL query statements. Take a look at the SQL created for this query 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;