In this article, we will learn how to sort records in a table, query, form, or report on one or more fields in MS Access. Sorting data can make a report or form more effective and easy to use. For example, a product catalog that lists the products in alphabetical order or by unit price is much easier to use than one that lists the items in random order.
Sorting records in Datasheet view
One way to sort records is by using the Datasheet view of a table or query. You can sort records on text, numeric, or date values by using the Ascending or Descending commands on the Home tab, in the Sort & Filter group. You can also sort records on multiple fields by using the Advanced Filter/Sort command. The order of the fields in the design grid determines the order of the sort. The first field is the outermost sort field, and the last field is the innermost sort field.
For example, suppose we have a table called Products with the following fields and records:
| ProductID | ProductName | Category | Price |
|---|---|---|---|
| 1 | Apple | Fruit | 0.5 |
| 2 | Banana | Fruit | 0.4 |
| 3 | Carrot | Vegetable | 0.6 |
| 4 | Date | Fruit | 0.7 |
| 5 | Eggplant | Vegetable | 0.8 |
If we want to sort the records by Category in ascending order and then by Price in descending order, we can do the following steps:
- Select the Category field in the Datasheet view.
- Click the Home tab, and then click Ascending in the Sort & Filter group.
- Click Advanced, and then click Advanced Filter/Sort.
- A new document tab appears, containing a design grid and a window in which you can select a field on which to sort.
- In the window, double-click the Price field to add it to the grid.
- In the Sort row for the Price column, click Descending.
- Click Toggle Filter to apply the sort order.
The records will now be sorted as follows:
| ProductID | ProductName | Category | Price |
|---|---|---|---|
| 5 | Eggplant | Vegetable | 0.8 |
| 3 | Carrot | Vegetable | 0.6 |
| 4 | Date | Fruit | 0.7 |
| 1 | Apple | Fruit | 0.5 |
| 2 | Banana | Fruit | 0.4 |
Sorting records in custom order
Sometimes, you may want to sort records in a custom order that is not based on alphabetical or numerical values. For example, you may want to sort weekdays so that Monday appears first, or sort employee titles by rank. In this case, you can use one of the following methods:
- Use the IIf function to assign numeric values to the field values and sort on those values.
- Create a lookup table that contains the field values and their corresponding ranks, and then join the lookup table with the original table and sort on the rank field.
For example, suppose we have a table called Employees with the following fields and records:
| EmployeeID | FirstName | LastName | Title |
|---|---|---|---|
| 1 | Alice | Smith | Sales Manager |
| 2 | Bob | Jones | Vice President, Sales |
| 3 | Charlie | Brown | Sales Representative |
| 4 | David | Lee | Inside Sales Coordinator |
If we want to sort the records by Title in a custom order, we can use the IIf function as follows:
- Open the table in Datasheet view.
- Click the Home tab, and then click Advanced Filter/Sort.
- Add the fields you want, such as FirstName, LastName, and Title, to the grid.
- Right-click the Field row in the first blank column, click Zoom, and then type the following expression:
IIf ( [Title] = "Vice President, Sales", 1, IIf ( [Title] = "Sales Manager", 2, IIf ( [Title] = "Sales Representative", 3, IIf ( [Title] = "Inside Sales Coordinator", 4, 5))))
- In the Sort row for the column containing the expression, click Ascending.
- Click Toggle Filter to apply the sort order.
The records will now be sorted as follows:
| EmployeeID | FirstName | LastName | Title |
|---|---|---|---|
| 2 | Bob | Jones | Vice President, Sales |
| 1 | Alice | Smith | Sales Manager |
| 3 | Charlie | Brown | Sales Representative |
| 4 | David | Lee | Inside Sales Coordinator |
Alternatively, we can create a lookup table called Titles with the following fields and records:
| Title | Rank |
|---|---|
| Vice President, Sales | 1 |
| Sales Manager | 2 |
| Sales Representative | 3 |
| Inside Sales Coordinator | 4 |
Then, we can join the Titles table with the Employees table on the Title field, and sort on the Rank field. To do this, we can create a query as follows:
- Click the Create tab, and then click Query Design.
- Add the Employees and Titles tables to the query, and join them on the Title field.
- Add the fields you want, such as FirstName, LastName, and Title, to the grid.
- Add the Rank field from the Titles table to the grid, and set its Sort row to Ascending.
- Set its Show row to No, so that it does not appear in the query results.
- Run the query to see the sorted records.
The records will now be sorted as follows:
| FirstName | LastName | Title |
|---|---|---|
| Bob | Jones | Vice President, Sales |
| Alice | Smith | Sales Manager |
| Charlie | Brown | Sales Representative |
| David | Lee | Inside Sales Coordinator |
Saving a sort order with an object
You can save a sort order with a table, query, form, or report, so that it becomes the default sort order for that object. To do this, you can use the following steps:
- Open the object in the appropriate view, such as Datasheet view for a table or query, or Form view for a form.
- Apply the sort order you want by using the methods described above.
- Click the File tab, and then click Save.
The next time you open the object, the records will be sorted in the same order.
