The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
April 2006

Sorting 101 – The First Shall Be Last and the Last Shall Be First

How do you get to be at the front of the line? Well, it really depends. If the line is ordered by last name and your name is Zwolinski, you’ll be near the tail end. Unless of course it's reverse alphabetical, then you're looking pretty good.

If the line is ordered by height, then last name is irrelevant. It’s possible to order a line by more ways than one: oldest to youngest, and then alphabetical. The order can also be really unique, like show Joe Jones first, and then show everybody else alphabetically by last name.

Why all this talk about sorting? Every sorting scenario mentioned above is possible in Access (and more!) In this month’s Access Wizard, we'll start to explore sorting.

In this issue
  • Tip of the Month - Sorting on the Fly
  • Sorting 101
  • Sorting 102 – Sorting on More than One Field at a Time
  • Conclusion and a Look Ahead
  • Trap of the Month – Sorting in Reports is not what you Expect

  • Sorting 101

    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.

    Sorting 102 – Sorting on More than One Field at a Time

    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.

    Conclusion and a Look Ahead

    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.

    Trap of the Month – Sorting in Reports is not what you Expect

    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.

    Tip of the Month - Sorting on the Fly

    Whenever you're looking at a table or the results of a query you can almost always quickly sort your data in any column. At the top of the screen look for the following symbols:

    If you see these, you can sort in any column. Highlight the column you want to sort– you do this by clicking on the column title. Then just press either the A to Z sort button or the Z to A sort button and your results will immediately sort themselves to the order you selected.

    This trick works for text columns, numeric columns, even date columns. It's quick and simple.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886