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.
Suppose we have the below table that contains employee data.
|Employee ID||Employee Name||Department||Team||Salary|
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.
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.