Others

How to Sort Microsoft Access Records by a Custom Field Order

Tauseeq Magsi Oct 10, 2026 869 views

Question details

The user needs to sort records in Microsoft Access first by ID_History and then by a specific, non-alphabetical sequence of FieldName values.

How to Sort Microsoft Access Records by a Custom Field Order

Product: Microsoft Access
Device & OS: not provided
Scenario: Structuring an Access query or report where data must be ordered by a defined sequence: Date Created, Part Number, User Modified, Solution, and Status.
Observed behavior: The data needs to be sorted based on a predefined list of custom text values rather than standard ascending or descending alphabetical order.

Before you start

Before modifying your database structure, ensure you have saved a backup copy of your Microsoft Access database to prevent accidental data loss during query or table modifications.

Solution 1Recommended

Create a Lookup Table for Custom Sorting

Creating a dedicated reference table is the most reliable and maintainable method for applying custom sort orders in Microsoft Access queries.

Instead of hard-coding the sort order into a complex query expression using IIF or Switch functions, creating a reference table allows you to assign specific integer values to each text value. This makes your database highly scalable and easy to update later.

Create a Reference Table

Navigate to the 'Create' tab on the Ribbon and click 'Table Design'. Add two fields: 'FieldName' (set Data Type to Short Text) and 'FieldOrder' (set Data Type to Number). Make 'FieldName' the Primary Key.

Populate the Sort Sequence

Switch to Datasheet View and save the table (e.g., as 'tblSortOrder'). Enter your sequence exactly as required: 'Date Created' with FieldOrder 1, 'Part Number' with 2, 'User Modified' with 3, 'Solution' with 4, and 'Status' with 5.

Join Tables in Query Design

Open your main data query in Design View. Click 'Add Table' to add your new 'tblSortOrder' table. Click and drag the 'FieldName' field from your history table to the 'FieldName' field in the reference table to establish an Inner Join.

Apply the Custom Sort Order

Drag the 'FieldOrder' column from your reference table into the query design grid. In the 'Sort' row, select 'Ascending' under your 'ID_History' field (ensure it is placed to the left), and select 'Ascending' under the 'FieldOrder' field.

Create a Lookup Table for Custom Sorting

Database Design Tip: If your 'FieldName' column is storing attribute types rather than values, consider normalizing your database structure in the future. Proper normalization can eliminate the need for complex custom sorting.

Free Microsoft Office alternative

Discover WPS Office for Your Data Management Needs

While Microsoft Access handles complex relational databases, everyday data management, sorting, and reporting are often faster in a spreadsheet. WPS Spreadsheet offers a lightweight, highly compatible alternative for analyzing large datasets without the steep learning curve of Access.

1. Export Access Data: Export your Microsoft Access table or query as an Excel Workbook (.xlsx) or CSV file.
2. Open in WPS Spreadsheet: Launch WPS Office, open the exported file in WPS Spreadsheet, and select the data range you want to organize.
3. Apply Custom Sort: Navigate to the Data tab, click Sort, and choose 'Custom Sort' to easily define your exact sorting sequence without writing SQL queries.

Free and lightweight Microsoft Office alternativeFully compatible with Microsoft Excel (.xlsx) and CSV formatsIntuitive Custom Sort feature for complex data sequencingFamiliar UI ensures zero learning curve for seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Can I custom sort in Access without creating a lookup table?

Yes, you can use the Switch() or nested IIf() functions directly in the query design grid to assign numerical values to text fields (e.g., Switch([FieldName]='Date Created',1, [FieldName]='Part Number',2)). However, this method is difficult to maintain if you have many fields or frequent changes.

Why is my custom sort order being ignored in Access Reports?

Microsoft Access Reports often override the sorting defined in their underlying queries. To apply your custom sort, open the Report in Design View, click 'Group & Sort' on the Ribbon, and add your 'FieldOrder' field as a primary sorting level.

What happens if a FieldName value is missing from my lookup table?

If you use a standard Inner Join, any records with a FieldName not listed in your lookup table will be excluded from the query results. To include them, double-click the join line in Query Design and change it to a Left Join, which will include all history records and leave the missing sort values blank.

Office Quick Solutions