Sort Microsoft Access Crosstab Query Rows in a Custom Order
Question details
The user needs to display Microsoft Access crosstab query rows in a business-defined sequence for attributes such as inventory, warehouse, and advertising.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Arrange crosstab row headings in a custom sequence rather than alphabetical or numeric attribute order.
- Observed behavior
- The desired result is a crosstab whose rows follow an explicitly assigned business order.
Open the crosstab query in Design View and identify its source table and attribute row-heading field. Determine the desired sequence and make sure each attribute has one consistent sort value across its source records.
Add a numeric SortOrder field and sort the crosstab by it
Use a stored numeric rank to control row order and make future sequence changes easier to maintain.
Access sorts query results in ascending or descending order using fields or expressions. A numeric SortOrder field translates your business-defined sequence into an ordinary ascending sort.
In the Navigation Pane, right-click the source table and select Design View. Add a field named SortOrder, set its Data Type to Number, and save the table.
Open the table in Datasheet View and enter the desired ranks, such as Inventory = 10, Warehouse = 20, and Advertising = 30. If an attribute appears in multiple records, give all of those records the same rank.
Right-click the crosstab query in the Navigation Pane and select Design View. Add SortOrder to the query grid, set its Total row to Group By, set its Crosstab row to Row Heading, and set its Sort row to Ascending. If the crosstab uses an intermediate source query, add SortOrder to that query first so it is available.
On the Query Design tab, click Run. Verify that the attribute rows follow their assigned ranks. If another field also has a Sort setting, clear it or place SortOrder to its left in the design grid so the custom rank is the primary sort.

Use WPS Spreadsheets to Organize Exported Access Results
WPS Office is a free, lightweight alternative for working with Access results exported to Excel. Its familiar spreadsheet interface and Excel-format compatibility make migration straightforward. WPS does not fix Access crosstab queries or replace the Access database engine, but WPS Spreadsheets can sort exported rows using a numeric SortOrder column.

Frequently Asked Questions
Does SortOrder change the physical order of records in the Access table?
No. Setting SortOrder to Ascending in the query's Design View controls the query results. It does not establish a permanent physical order for table records; use an explicit sort whenever display order matters.
Can I use nested IIf expressions instead of adding a field?
Yes. In Query Design View, you can create a calculated field that assigns a numeric rank to each attribute, then use it as a grouped row heading and sort it ascending. However, nested IIf expressions become harder to maintain as the sequence grows or changes.
How do I insert a new attribute between two existing rows?
Open the source table in Datasheet View and assign the new attribute a rank between the existing values. For example, if Inventory is 10 and Warehouse is 20, give the new attribute 15. Run the crosstab query again to apply the sequence.
Why did one attribute split into multiple crosstab rows after I added SortOrder?
The crosstab groups by both the attribute and SortOrder when both are row headings. Open the source table in Datasheet View and check whether records for that attribute have different ranks. Assign them the same SortOrder value, then rerun the query.
Can WPS Office run my Access crosstab query?
No. WPS Office is not an Access database engine. Run the query in Microsoft Access, then use External Data > Export > Excel to export its results. Open the exported spreadsheet in WPS Spreadsheets to review or sort the rows.




