Creating Queries  «Prev  Next»
Lesson 12 Specifying a sort order
Objective Refine your query by specifying a sort order in MS Access

Refine a Query by Specifying Sort Order in Microsoft Access 365

When you first build a query in Microsoft Access 365, the records may appear in a default order (for example, by primary key or by insertion sequence). You can use the Sort tools directly in Datasheet View, but if you want the query to open in a consistent order every time—or use it as the basis for a form or report—you should define the sort order in Query Design View.

By setting sort order in the design grid, you:
  • Control exactly how records are ordered when the query runs.
  • Ensure forms and reports based on the query inherit the sort sequence.
  • Can define multi-level sorting (for example, by Company, then by Project, then by Date).

Datasheet Sorting vs. Design View Sorting

In Datasheet View, you can click a column and use the Sort Ascending or Sort Descending buttons. This is useful for quick, temporary sorting.

In Design View, you define sort order as part of the query itself, using the Sort row in the design grid. This sort order is saved with the query and applies every time the query runs, including when it is used as the record source for forms and reports.

Using the Sort Row in the Query Design Grid

To specify a sort order in Design View:

  1. Open the query in Design View from the Navigation Pane.
  2. Locate the field you want to sort by in the design grid (for example, Company).
  3. In that field’s column, click in the Sort row.
  4. Use the drop-down list to choose:
    • Ascending – A to Z for text, earliest to latest for dates, smallest to largest for numbers.
    • Descending – Z to A for text, latest to earliest for dates, largest to smallest for numbers.
    • (Not Sorted) – No sort is applied by this field.
  5. Switch to Datasheet View to see the new sort order applied.

Example: Sorting by Company

Imagine a query that lists hours worked on consulting projects. At first, the records may appear sorted by date or by an ID field. To show results grouped and ordered by Company:

  1. Open the query in Design View.
  2. Find the Company field in the grid.
  3. Set the Sort row for Company to Ascending.
  4. View the datasheet to confirm that companies are now listed alphabetically.

If you have many records for a single company (for example, all rows for ABC Webworks), you can refine the order further with a secondary sort.

Defining a Secondary (and Tertiary) Sort Field

When you assign sort order to more than one field, Access sorts from left to right in the design grid:

To create a common multi-level sort:

  1. Set Company to Ascending (primary sort).
  2. Set ProjectID or Project Description to Ascending (secondary sort).
  3. Optionally set Date to Ascending (tertiary sort).
  4. Ensure these fields appear in the grid from left to right in the order you want Access to evaluate the sort.

Now, when you view the datasheet:

Practical Example: Custom Sort Order for Categories

Sometimes you want a sort order that is not strictly alphabetical or numeric. For example, suppose you track weekly work items in categories such as:
  • Customer
  • People Management
  • Internal Projects
One manager might want Customer at the top, while another might prefer People Management first. In scenarios like this, a common technique in Access is:
  1. Add a numeric field such as CategorySortOrder to your category table.
  2. Assign values that represent the desired order (for example, Customer = 1, People Management = 2, Internal Projects = 3).
  3. Include both CategorySortOrder and CategoryName in your query.
  4. Sort by CategorySortOrder (Ascending) and then by CategoryName.
This approach gives you a flexible, user-controlled sort order that can be reused in forms, reports, and email summaries.

Tips for Working with Sort Order

Specifying Sort Order - Exercise

Practice refining your query by defining a sort order.
Specifying Sort Order - Exercise

SEMrush Software