More Query Design Options

In Access query design is essential for getting the data you want. Use query design in Access to design almost any query you want.

Sorting queries

Access allows you to apply multiple sorts at once while you're designing your query. This allows you to view your data exactly the way you want, every single time you view it.

A sort that includes more than one sorted field is called a multilevel sort. A multilevel sort allows you to apply an initial sort, then further organize data with additional sorts. For instance, if you had a table full of customers and their addresses, you might choose to first sort the records by city, then further sort them alphabetically by last name.

When more than one sort is included in a query, Access reads the sorts from left to right. This means the leftmost sort will be applied first. In the below example, customers will be sorted first by the City they live in and then by the Zip Code within that city.

A multi-level sort. The records will be sorted by City first.A multilevel sort. The records will be sorted by City first.

To apply a multilevel sort:

  1. Open the query and switch to Design view.
  2. Locate the field you want to sort first. In the Sort: row, click the drop-down arrow to select either an ascending or descending sort.
    Applying an Ascending sort to a fieldApplying an Ascending sort to a field
  3. Repeat the process in the other fields to add additional sorts. Remember, the sorts are applied from left to right, so any additional sorts must be applied to fields located to the right of your primary sort. If necessary, you can rearrange the fields by clicking a field and dragging it to a new location.
    A multi-level sortA multilevel sort
  4. To apply the sort, click the Run command.
    The Run Query commandThe Run Query command
  5. Your query results will appear with the desired sort.
    The sorted query resultsThe sorted query results

You can also apply multilevel sorts to tables that don't have queries applied to them. From the Home tab on the Ribbon, select the Advanced drop-down command in the Sort & Filter group. Select Advanced Filter/Sort, and create the multilevel sort as you normally would. When you're finished, click the Toggle Filter command to apply your sort.

The Advanced Filter/Sort commandThe Advanced Filter/Sort command