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:

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.