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.