22 min read
Totals Queries: Summarizing Data (Group By, Sum, Count)
A Totals query groups records together and calculates a summary for each group — similar in spirit to a spreadsheet's SUMIF or a PivotTable.
You will learn: the Total row and Group By, Count, Sum. You will do: count loans per member. sum unpaid fines per member.
Turn On Totals
Goal
Enable the Total row on the design grid.
Totals squash many rows into summary numbers.
Open a query in Design View
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- Each column gets a function dropdown: Group By, Count, Sum, Avg, and others.
Count Loans per Member
Goal
Count how many loan rows each member has.
Group By on the name. Count on LoanID.
Add both tables
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- One row per member who has at least one loan.
- Count of LoanID shows the number of loan rows per member.
SOURCE DATA
| Loans | ||||
|---|---|---|---|---|
QUERY DESIGN
| Grid — LoanCountByMember | |
|---|---|
RESULT
| Result: LoanCountByMember | |
|---|---|
Sum Unpaid Fines
Goal
Total unpaid fine money per member.
Join Members → Loans → Fines. Filter Paid = No.
Add all three tables
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- Each row shows a member name and Sum of Amount for unpaid fines only.
SOURCE DATA
| Fines | ||||
|---|---|---|---|---|
QUERY DESIGN
| Grid — UnpaidFinesByMember | ||
|---|---|---|
RESULT
| Result: UnpaidFinesByMember | |
|---|---|
Count Open Loans Only
Add ReturnDate with Criteria Is Null. Count LoanID with FullName Group By.
Save as OpenLoanCountByMember. Sophie Walsh and Amelia Jones each show 1 — only open loans count.
| Result: OpenLoanCountByMember | |
|---|---|
Practice
Swipe or use the arrows to move between questions.
1 / 2
Field Notes: Grouping and Summarizing
Goal
Group records and calculate a summary for each group.
A Totals query answers questions like "how many orders per region?" or "total sales per month?" without needing a spreadsheet.
Add the group and summary fields
Do this
Verification
Check your work
You are correct if all of these are true:
- Run the query to see one summarized row per group — for example, total sales for each region.
More lessons in Microsoft Access · Next: Action Queries: Update, Append, Delete, Make-Table · Previous: Querying Multiple Related Tables