Grouping and Sorting Reports in MS Access

Grouping means arranging records that share the same value in a field. For example, students can be grouped according to their course.

Example: Students Table

If you group this data by Course, students enrolled in the same course appear together.

Benefits of Grouping

• Organizes related records.
• Makes large reports easier to understand.
• Helps prepare course-wise or department-wise reports.
• Allows group-level calculations in suitable reports.

Sorting means arranging records according to a field's values. You can sort names alphabetically, dates chronologically, or numbers by their values.

Types of Sorting

Examples

• Sort StudentName from A to Z.
• Sort AdmissionDate from oldest to newest.
• Sort FeeAmount from highest to lowest.
• Sort StudentID from smallest to largest.

You can manage grouping and sorting settings through the report design tools.

Steps

1. Open your Microsoft Access database.
2. Find the required report in the Navigation Pane.
3. Right-click the report.
4. Select Design View.
5. Open the Design tab on the ribbon.
6. Locate the Group & Sort command.
7. Click it to display the grouping and sorting pane.

The Group, Sort, and Total pane lets you add and manage grouping and sorting levels for the report.

Suppose you want to create a report that displays students separately according to their courses.

Steps

1. Open the report in Design View.
2. Click the Design tab.
3. Open the Group & Sort pane.
4. Click Add a group.
5. Select the Course field.
6. Review the grouping options.
7. Save the report.
8. Open the report to check the result.

Expected Report Structure

This is an illustrative example. Your report's exact appearance depends on its layout and grouping settings.

You can sort students alphabetically by StudentName so that users can find names more easily.

Steps

1. Open the report in Design View.
2. Open the Group & Sort pane from the Design tab.
3. Click Add a sort.
4. Select StudentName.
5. Choose ascending order for A to Z.
6. Save and preview the report.

Example: Ascending Order

Multiple sorting levels allow you to sort records using more than one field.

Example

First, sort students by Course in ascending order. Then, sort students within each course by StudentName in ascending order.

Steps

1. Open the report in Design View.
2. Open the Group & Sort pane.
3. Add Course as the grouping or sorting field, as required.
4. Add StudentName as another sorting level.
5. Set the desired order for each field.
6. Save and preview the report.

Example Result

The example assumes that Course is grouped in ascending order and StudentName is sorted alphabetically within each group.

Group headers and footers help display information related to a particular group.

• Group Header: Displays a heading or field value at the beginning of a group.
• Group Footer: Displays information at the end of a group, such as a subtotal.

Example

A course-wise report may display the course name in the group header and the number of students in the group footer.

Steps

1. Open the report in Design View.
2. Open the Group & Sort pane.
3. Find the grouping level you want to configure.
4. Use the More options to review available settings.
5. Enable a group header or footer if required.
6. Place the necessary controls in the appropriate section.
7. Save and preview the report.

A report can display summary information, such as the total fees collected or the number of students in a course.

Examples of Summary Information

• Count of students in each course.
• Sum of fees collected by course.
• Average marks for each class.
• Maximum or minimum value in a group.

Example: Course Fee Summary

The values above are examples only.

To calculate totals, use appropriate aggregate functions and summary controls in the report. The underlying data must contain the fields needed for the calculation.

You can modify or remove a grouping or sorting level when the report requirements change.

To Edit a Level

1. Open the report in Design View.
2. Open the Group & Sort pane.
3. Select the required grouping or sorting level.
4. Change its field or sorting order using the available options.
5. Save the report.

To Remove a Level

1. Open the Group & Sort pane.
2. Locate the level you want to remove.
3. Open its More options menu.
4. Select the available option to remove the group or sort.
5. Save and review the report.

Create a course-wise student report with alphabetical sorting.

Required Fields

• StudentID
• StudentName
• Course
• FeeAmount

Tasks

1. Create a Students table with sample records.
2. Create a report named rptCourseWiseStudents.
3. Open the report in Design View.
4. Group records by Course.
5. Sort StudentName in ascending order.
6. Display a group header for the course name.
7. If suitable, add a count of students to each group.
8. Preview and save the report.

1. What is grouping in MS Access reports?
2. What is sorting in reports?
3. What is the difference between grouping and sorting?
4. Which pane is used to manage report grouping and sorting?
5. How can you group students by Course?
6. What is ascending sorting?
7. What is descending sorting?
8. What is a group header?
9. Why are group footers useful?
10. Can a report use multiple sorting levels?