Sort Data in Access Report

This Microsoft Access tutorial explains how to sort data in Access Report, including sort data in custom order.

Sort Data in Access Report

In Access Report Design View, you can sort data at the Group, Sort, and Total section, in this tutorial I will explain how to sort data in Detail and sort Grouped data.

Example

Suppose we have the below table that contains employee data.

employee_tbl
Employee ID Employee Name Department Team Salary
001 David HR Compensation 10000
002 Mary Finance Reporting 20000
003 Peter HR HR System 30000
004 Apple HR Compensation 40000
005 June Finance Operation 50000

Sort Data in Detail in Access Report

Suppose we want to sort by Employee ID in descending order.

Create a Report using employee_tbl as Record Source.

Add Employee ID field in Detail.

Under Group, Sort, and Total, Add a sort > Sort by Employee ID > with Z on top

 

Run the Report, now Employee ID is sorted in descending order.

Sort Group Data in Access Report

Now Group the Employee ID by Department, and sort Department in descending order.

Under Group, Sort and Total, Add a group > Group on Department > with Z on top

 

Run the Report, now the data is sorted by Department in descending order and then by Employee ID in descending order.

Sort Multi Group Data in Access Report

You can add more Group by and sort the groups. For example, you can Group by Department and then Group by Team in descending order as below.

Custom Sorting in Access Report

As you can see in the Sort by option, you can order either with A on top or with Z on top. To do a custom sorting, you have to create an assist column in the Record Source.

Suppose we have created a Table that defines the Employee ID sorting order.

employee_order_tbl
Employee ID Order
001 1
002 4
003 2
004 5
005 3

 

Create a Query that contains the sorting order.

 

Use the Query as Report Record Source, and then Sort by Order as below.

 

Run the Report, now Employee ID is sorted in custom order.

 

Sorting is still not working, why?

Sometimes the sorting on Group on is being ignored, I believe it is because the lower level sorting is influenced by the upper level sorting.

To solve the issue, add a Sort by before Group On as below.

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *