Grouping a report means arranging records under shared headings and adding counts or subtotals for each heading. The safe version keeps every required field visible, and the unsafe version collapses the report to summary rows only.
This lesson is part of database queries and reports and builds on sorting with a secondary key, because groups usually follow a sort.
How do you group safely?
- List the fields the task requires in the report.
- Choose the grouping field and sort by it first.
- Add the summary values the task asks for: count, sum or average, for each group and overall.
- Keep the detail rows unless the task says to show the summary only.
- Tick each required field off in the finished report.
Worked example
Using the invented Harbour Hill Clubs table from the module page.
Task: group by Club. Show Name, Year and Fee for each member. Add the number of members and the total fee for each club, and a grand total of fees.
Step 1, group and sort: Chess, Drama, Robotics.
Step 2, Chess: Aiman, Dewi, Hui, Imran. Four members. Fee total 30 + 30 + 30 + 30 = 120.
Step 3, Drama: Bella, Chen, Gopal. Three members. 45 + 45 + 45 = 135.
Step 4, Robotics: Evan, Farah, Jia. Three members. 60 + 60 + 60 = 180.
Step 5, grand total: 120 + 135 + 180 = 435. A second check: 4 members at RM30, 3 at RM45 and 3 at RM60 gives 120 + 135 + 180 again, so 435.
The ten detail rows stay on view under their three headings, along with the three subtotals and the grand total.
The mistake to watch for
Switching the report to summary only:
The report shows Chess 4 and 120, Drama 3 and 135, Robotics 3 and 180, and nothing else.
The totals are right, but Name, Year and Fee for each member have vanished.
The task asked for them, so marks are lost. The correction is to turn the detail rows back on. A related slip is to forget the grand total, so tick off every item in the task list at the end.
Check yourself
1. Group by Year. Give the count and fee total for each year.
Show answer
Year 9: Dewi, Gopal, Jia, so 3 members and 30 + 45 + 60 = 135. Year 10: Aiman, Chen, Farah, Imran, so 4 members and 30 + 45 + 60 + 30 = 165. Year 11: Bella, Evan, Hui, so 3 members and 45 + 60 + 30 = 135. Grand total 435 (135 + 165 + 135).
2. What is the average number of sessions for each club?
Show answer
Chess: (8 + 11 + 10 + 12) / 4 = 41 / 4 = 10.25. Drama: (12 + 9 + 6) / 3 = 27 / 3 = 9. Robotics: (12 + 7 + 5) / 3 = 24 / 3 = 8.
3. A grouped report shows Robotics with a count of 2. What should you check first?
Show answer
The source table has 3 Robotics members, so a record is missing from the report. Check for a leftover filter or criteria, then re-run and compare with the source. This is the method in comparing displayed results with the source.
Where this leads next
Finish the module with comparing displayed results with the source dataset, then try the practice set. The ICT practical task and evidence checker can help you tick requirements off.
Reports that tick every box take habit, and our teachers can build it with you in online one-to-one ICT tuition.