How to Sort Data in Access 2016 - dummies

How to Sort Data in Access 2016

By Laurie Ulrich Fuller, Ken Cook

Very few Access databases are organized into nice, convenient alphabetical lists. You don’t enter your records alphabetically; you enter them in the order they come to you. So what do you do when you need a list of products in product-number order or a list of addresses in zip code order right now?

Sorting by a single field

The solution lies in the sort commands, which are incredibly easy to use. The sort commands are on the Ribbon’s Home tab, in the Sort & Filter section. The two buttons (Ascending and Descending) do the job quite well:

  • Sort Ascending sorts your records from top to bottom:

    • Records that begin with A are at the beginning, and records that begin with Z are at the end.

    • If your field contains numeric data (whether they’re true numbers or Short Text fields storing zip codes or Currency fields storing prices), an Ascending sort puts them in order from lowest number to highest.

  • Sort Descending sorts your records from bottom to top:

    • Records that begin with Z are at the top, and records that begin with A are at the bottom of the list.

    • If your field contains numeric data, a Descending sort puts the records in order from highest number to lowest.

If, after sorting in Ascending or Descending order you want to put things back the way they were – in the order just prior to clicking either of those buttons – click the Remove Sort button, located just below the Ascending and Descending buttons in the Sort & Filter group.

Sorting on more than one field

What if you want to sort by multiple fields – such as by zip code, and then within that sort, you want all the people with the same zip code to appear in Last Name order? Or maybe you want to sort all your customers by a Status field and then within each group of customers that creates, to sort by current balance? You’d need to use the following steps to nest one sort inside another.

You can sort by more than one column at a time like this:

  1. Click the heading of the first column to sort by.

    The entire column is highlighted.

  2. Hold down the Shift key and click the heading of the last column to sort by.

    All columns from the first one to the last one are highlighted.

  3. Choose either Sort Ascending or Sort Descending.

    The sort is always performed from left to right.

    In other words, you can’t sort by the contents of the fourth column and within that by the contents of the third column.

Sorting has its own peculiarity when working with numbers in a text field. When sorting a field that has numbers mixed in with spaces and letters (such as street addresses), Access ranks the numbers as though they were letters, not numbers. This behavior means that Access puts (say) “1065 W. Orange Street” before “129 Mulberry Street.” (Thanks to the peculiar way that your computer sorts information, the 0 in the second position of 1065 comes before the 2 in the second position of 129.)