Sort Data in Access Report

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

You may also want to read:

Add Auto number in Access Query

Add sorting in Access Table field

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.

 

 

Leave a Reply

Your email address will not be published.