Others

Group Subjects and Activities by Student in an Access Report

Phi Hung VoPhi Hung Vo Nov 14, 2026 868 views

Question details

Create a report that lists each student's subjects and activities under one student heading.

Group Subjects and Activities by Student in an Access Report
Product
Microsoft Access
Device & OS
not provided
Scenario
A report needs to show two categories of related records beneath each student without creating separate student hierarchies.
Observed behavior
The desired report has one student group containing both subject and activity entries.
Before you start

Identify the Students table, the tables containing subjects and activities, and the fields that link each record to a student. The example SQL uses junction tables; replace its table and field names with those in your database.

Solution 1Recommended

Combine subjects and activities with a UNION query

Use one report record source when you want subjects and activities listed as rows beneath each student.

A UNION query combines two result sets with matching columns. An ItemType field identifies each row as a subject or an activity.

The example assumes StudentSubjects links students to Subjects, and StudentActivities links students to Activities. Adjust the joins if your database stores these relationships differently.

1
Create the UNION query

In Access, select Create > Query Design, close the table selection window, and switch to SQL View. Enter: SELECT StudentSubjects.Student_ID, Subjects.SubjectName AS ItemName, 'Subject' AS ItemType FROM StudentSubjects INNER JOIN Subjects ON StudentSubjects.Subject_ID = Subjects.Subject_ID UNION ALL SELECT StudentActivities.Student_ID, Activities.ActivityName AS ItemName, 'Activity' AS ItemType FROM StudentActivities INNER JOIN Activities ON StudentActivities.Activity_ID = Activities.Activity_ID;

2
Save and check the results

Save the query as qryStudentItems and select Run. Check that each row has the correct Student_ID, ItemName, and ItemType.

3
Add student names

Select Create > Query Design and add Students and qryStudentItems. Join Students.Student_ID to qryStudentItems.Student_ID, add the student ID, name, ItemName, and ItemType fields to the grid, and save the query as the report's record source.

4
Build the grouped report

Select Create > Report Wizard and choose the saved report query. Add its fields, group by Student_ID, and place the student's name in the student group header. Use Group & Sort in Layout or Design View to group or sort by ItemType within each student, then sort by ItemName if needed.

Combine subjects and activities with a UNION query
Students without matching items: An inner join to qryStudentItems excludes students with no subjects or activities. If those students must appear, use a join that includes all records from Students and review how blank item rows display in the report.
Free Microsoft Office alternative

Review exported student lists in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface and support for common Microsoft Office formats. Export Access data to Excel to review it in WPS Spreadsheets; WPS does not create or repair Access reports.

Review and sort exported student, subject, and activity lists in a spreadsheet.Open and edit Microsoft Excel XLSX files for an easier transition.Use a familiar interface for everyday document and spreadsheet work.Work with a free, lightweight Office alternative.
microsoft office alternative - wps office

Frequently Asked Questions

Can I create a UNION query in Access Query Design?

Enter the UNION statement in SQL View. You can use Query Design to help build the individual SELECT queries, then combine their SQL statements.

How do I keep subjects and activities under one student heading?

In the report's Group & Sort pane, group first by Student_ID. Then group or sort by ItemType within that student group instead of creating separate student groups for the two categories.

Why is a student missing from the report?

An inner join between Students and the combined items query excludes students without matching items. In Query Design, change the join properties to include all records from Students if those students should appear.

How can I show subjects before activities?

In Group & Sort, apply a descending sort to ItemType when its values are Subject and Activity. Alternatively, add a numeric sort field to the query and sort by that field.

Can I build the report without writing SQL?

Yes. Create a main student report and separate subject and activity subreports, then set each subreport control's Link Master Fields and Link Child Fields properties to the matching student ID fields.