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

Sorting 103 – Sorting on Steroids

I'm writing this Wizard from Hong Kong. I'm here with my younger son Robert, visiting my older son, Jason, who is taking a semester to study here.

Hong Kong has a long and rich history and parts of Hong Kong Island are among the most densely populated places on earth. Tourism plays a large role in the economy of Hong Kong. Just a few years back there was an outbreak of SARS here and as a result tourism revenues plummeted.

As we entered customs on our arrival I saw two officials with surgical masks looking at computer monitors. As I passed by I glanced at the monitors and saw that they displayed each passenger as he came off the plane. As it turns out, they were checking body heat. Anyone who showed up in a much brighter hue than everyone else would be pulled aside and checked for diseases. Although this started several years back with the advent of SARS, today I'm sure they're also looking out for other diseases like bird flu.

In Hong Kong they strive very hard to control their environment; they make their expectations clear and they work to make sure that the unexpected does not happen. This level of control can be had in Access sorting as well -- this is another of those "stretch" segues – but the medical monitoring is interesting, isn't it? This month I’ll reveal a few tips to show you how to completely control sorting within Access.

In this issue
  • Tip of the Month – How to get the Ampersand (&) in a Form Label
  • Power and Flexibility without Constraints
  • Trap of the Month – A Sorting Hazard

  • In the last few issues of the Wizard, we've moved from simple sorting to sorting via artificial fields in a table and to artificial fields we've created on the fly. We've done this by either adding a sorting field to a table or through the use of Union Queries. Although I'm a fan of Union Queries, they are difficult to create and challenging to debug.

    This month we'll look at a technique that requires neither establishing a dedicated sorting field within a table, nor the need to resort to a union query. Instead, we'll use what I call “directive sorting”.

    Let's start again with our table of Massachusetts cities and populations.

    Our goal is to sort this list first by size and then alphabetically, except we want Boxboro to come first. We want to do this without creating an artificial field to aid us with the sort.

    The first step is to construct a query as follows:

    When we run this query we get:

    Now this is close to what we want, except the first city is Ashland rather than Boxboro. To solve this problem we go to the SQL view (see June 05 for details about how to uncover and understand the SQL statements behind a query.)

    For the query shown above, the SQL statement is:

    SELECT tlkpCities.Size, tlkpCities.CITY
    FROM tlkpCities
    ORDER BY tlkpCities.Size, tlkpCities.CITY;

    Notice that the Order By statement, which controls sorting, refers to two fields from the table tlkpCities: Size first and then CITY. This statement sorts first by size, 2500 being the smallest and 7500 is the largest. In the case where multiple cities have the same population, the SQL statement will keep all those records together and sort by the city name. Carlisle, Wayland and Westford all have a population of 7500, yet they sort alphabetically by the City name.

    So to get Boxboro to come first, we change the SQL statement as follows:

    SELECT tlkpCities.Size, tlkpCities.CITY
    FROM tlkpCities
    ORDER BY city = "Boxboro", tlkpCities.Size, tlkpCities.CITY;

    And when we run this we get:

    Just what we were looking for: Boxboro is first even though it's not the smallest.

    To pull this apart even more, let's take a look at the design view of this query:

    Notice that the first column has been changed to city = "Boxboro"; the query is sorting on that field. The logic involved is to evaluate the first column and determine if the value of city is equal to "Boxboro." If it is, it returns a true; otherwise it returns a false. It then sorts on this column putting any true values first (in our case we have one Boxboro) so that becomes our first record and then sorts by size and then finally by city.

    This is very powerful stuff. If we wanted to have Boxboro last we just change the Sort Order to Descending. We can even do this to more than one city. If we want Boxboro first, Westford second and then all remaining cities sorted by size and city name, we simply change our Order By clause as follows: ORDER BY city="Boxboro", city = "Westford", tlkpCities.Size, tlkpCities.CITY And we get

    We’ve forced Boxboro to occur first, followed by Westford, and then the rest of the cities follow, sorted first by size and then alphabetically by city name.

    Power and Flexibility without Constraints

    As you may have guessed there's no need to create this type of sorting query in the SQL window; you can do it in the Query by Design Window as well – it's purely a matter of personal preference.

    There's no end to what you can do with this technique. You can force multiple values to the top or bottom of the list and sort the remaining fields in any way that meets your needs. Best of all, this technique does not require the use of a dedicated field within a table or rely on the use of a union query!

    Trap of the Month – A Sorting Hazard

    Beware of sorting on too many fields if your table has many rows. In terms of computing costs, sorting is a very expensive process. There are many different techniques that can be used to sort data; luckily Access shields us from the complexity behind the scenes of sorting. For a typical sort, the number of rows in an Access Application does not get in the way. However, there are exceptions. I've seen Access Applications with millions of rows and have even created an application with over 6 million records. When you have many records, you'll want to minimize your sorting and do it only when truly needed. In addition, it’s important to not include sorting columns that you really don't need.

    In no case should you sort your data prior to sending it to a report. The Access reporting engine contains its own sorting mechanism and will completely ignore any sorting instructions contained in a SQL statement or query sending the data to the report.

    Tip of the Month – How to get the Ampersand (&) in a Form Label

    Every once in a while you'll want a label to contain the ampersand character (&). For instance, you may want to show a field with the label "Billy & Bob's Total." If you typed that text into a label, you would get "Billy _Bob's Total."

    In this case, Access is just trying to helpful. You see, Access uses the ampersand character to indicate a Hot-Key (To learn more about Hotkeys see the May 05 Issue). To get around the automatic editing by Access and to actually show the & character, simply double it. That is, type two symbols (&&) where you would normally type a single &. Access interprets that as an ampersand and correctly converts the double ampersand to a single one on the screen.

    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