Last month, we saw how to sort in a query. We were sorting a list of cities in Massachusetts. We used the following as our starting point:

Figure 1

When this is run we get

Figure 2

Nothing especially exciting here-just the standard alphabetical sorting by the name of the city. If we want a reverse sort (Z – A), we would replace Ascending with Descending in the Sort field as shown in Figure 1.

To demonstrate more powerful sorting techniques in Access, I've added a second column to our table of cities in Massachusetts. Take a look at our new table:

Figure 3

This new field - Size - captures the population of each city or town (my numbers are made up, so no need to email me that I need to brush up on demographics).

Now let's say we want to sort our list by size (largest first) and then by city or name in alphabetical order. Our query in this case will look like this:

Figure 4
And when we run this we get

Figure 5

Well, this is nice but what if we want to show the City column first and then the Size column next? If we just reverse the columns in our query like this

Figure 6

We get

Figure 7

These are not the same results with the columns in a different order – our biggest city, Carlisle with 7500 should be first, not Acton with 5000. The way Access determines its sorting logic is that it goes left to right. So if you look at figure 6 what we've told Access to do is sort on the City name and then sort on the Size. We really want the largest city first followed by an alphabetical sort by City name when the cities are the same size. So to solve this problem we do the following:

Figure 8

Notice that we now have two Size columns. The first Size column lays out the first sorting condition that must be considered: sort the records in descending order by the Size column. Notice, however, that the show checkbox is not checked for this column. The second column gives the next sorting condition: sort the cities in ascending alphabetical order AFTER the Size in descending order. In this case, the Show box is checked so the attribute in this column (City) will be shown. Our final column is the Size column repeated; there's no need to sort this column because we've already sorted it in the first column. However, since we want to show it to the right of the City column, we check the box in the show row.

When we run this we get what we're looking for:

Figure 9

Our list of cities is sorted by size then by city names, with the city as the first column.

This month we’ve touched on a few Access sort options beyond the simple straightforward alphabetical sort. There's more to come in next month’s Access Wizard, including some very powerful and flexible routines.

Sorting in reports is different than sorting in tables or queries. It's natural to think that a report will sort in the same order that the underlying query is sorted. This is not the case. Reports are sorted via a sorting and grouping mechanism that we'll cover in a future issue.