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

Sorting 102 –From the Tallest to the Smallest (unless your name is Hollingsworth Billingsly)

When I was in grade school (many, many moons ago) my second grade teacher, Mrs. Byorkman, often had us line up in order by height: shortest to tallest. Today she would line us up from most to least vertically challenged. She always made one exception to the order for whoever was serving as the monitor of the week (the teacher's pet, if you will).

There was one kid in my class that went out of his way to make Mrs. Byorkman happy -- Hollingsworth Billingsly. Now Hollingsworth wasn't especially popular amongst the rest of us kids as we were pretty rough and tumble, but old Hollingsworth would pick up pieces of litter in the classroom, bring Mrs. Byorkman apples (yeah – kids really did stuff like that back then) and generally engage in teacher – pleasing behavior. As a result Hollingsworth was frequently the class monitor. So when it came time to line up she'd say "Class, it's time to line up -- smallest to tallest, except for you Hollingsworth, you go to the head of the line."

Last month we looked at some simple sorting techniques: sorting on more than one field and arranging the display of columns in an order that is different than the default sorting precedence. This month we’ll tackle a tougher sorting problem – how to sort using a rule (like smallest to tallest) and incorporating exceptional cases (like putting a certain value first). We are now entering the realm of advanced sorting techniques!

In this issue
  • Tip of the Month – Right Click for Quick Sorting
  • Sorting by Adding a Field
  • Sorting with Virtual Fields
  • Conclusion and a look ahead
  • Trap of the Month - Tables Do Not Have an Order

  • Sorting by Adding a Field

    Let's continue working with our list of Massachusetts cities. To refresh your memory, here is the table we've been using:

    We can make one easy change to the structure of this table that will give us powerful sorting capabilities.

    Note that we've added one field "fldSort" that we can use in our sorting to force a value or set of values to the top. We can manipulate or force the sort order artificially by inserting a number into the sorting column that is either higher or lower than the default value, in our case 99.

    For instance, let's say we want to sort cities first by size and then alphabetically, except we want Boxboro to come first.

    With the number 1 in the Sorting column for Boxboro (as shown in the table above), let's create a query like this:

    Notice that I've put the fldSort field first and unchecked the show checkmark. As we learned last month, the order of the sort fields in the query (from left to right) sets the sort precedence. In this case, the records will be sorted first by the fldSort values in ascending order, and then by size, and lastly alphabetically by name. The fact that I’ve unchecked the “Show” box means that the values in the fldSort field will not be shown in the query results.

    When we run this we get:

    Boxboro is at the top of the list, even though it's not the smallest city nor is it the first alphabetically. It’s at the top simply because we forced it by adding an additional sorting field.

    Sorting with Virtual Fields

    This technique of adding a sort column to a table that needs to be periodically sorted in some unusual way is a very powerful tool. The addition of the added column allows easy and precise control of the order that values will appear.

    But let's go back to what started this whole sorting issue. Remember the need to have a lookup table with an "Add New" at the top? We covered that problem in some depth in the March 06 issue. To refresh your memory we were trying to produce the following list:

    We achieved this sort order with a union query as follows:

    Select "(ADD NEW)" as City from tlkpCities
    SELECT tlkpCities.CITY FROM tlkpCities ORDER BY CITY;

    We used a union query and sorted the "(Add New)" value to the top by making sure it had a character that sorted correctly (in this case, the parenthesis forced the value to sort at the top). But let's take it a step further. What if we want "Add New" to appear at the top without the parenthesis? "Add New" is not in our table as a city, and it shouldn't be because it's not a city. So we can't use the technique of adding a dedicated sort column in our table – but there is a way.

    What we can do is add an artificial column on the fly. For instance take a look at the following query:

    Here we've added a column to our query result. We call the column "SortByMe" and we've given it a value of 19. Note that this column does not appear in our table, we're simply adding it to the query results.

    When we run this we get:

    Every Sort By Me column has a value of 19.

    We can take this example of creating an artificial sort column and add it to our union query (if you need a refresher on Union queries see Union Queries – The Urge to Merge) and reconstruct it as follows:

    Select "ADD NEW" as City, 1 as SortbyMe from tlkpCities
    SELECT tlkpCities.CITY, 99 as SortByMe FROM tlkpCities ORDER BY SortByMe, CITY;

    And when this runs we get:

    What we’ve done is used the Union query to assign SortbyMe values on the fly. “ADD NEW” was assigned a sort value of 1, while the rest of the cities were assigned a sort value of 99. The records were sorted first by SortByMe values, and then alphabetically by city. As a result, ADD NEW comes before Acton, even though alphabetically it shouldn't. The results were achieved by adding a sort column through the query statement construction. This is a powerful technique to populate a combo box with Add New (or whatever verbiage we want) as the first item in the list. Be sure to hide the second sorting column! Then if our user chooses Add New we can respond to that selection differently than if they choose something else on the list. Watch future issues for techniques we can use to handle this selection.

    Conclusion and a look ahead

    This month we looked at two different sorting techniques using fields in a query. The first technique, adding a sort column to an existing table, allows us to achieve a fine level of sorting with very little work. This technique worked in this case because adding an additional field to the table was easy and didn’t complicate the records. All of the values in the city field were consistent---they were all city names.

    The second technique was employed in the example where it was impractical to add a field to a table. In the second case, we had a value ADD NEW in the city column, which was not a city name. We demonstrated that it is possible to float selected items to the top of a sorted list by adding a virtual field on the fly through a Union query.

    There are yet more ways to sort and in a future issue we'll look at sorting techniques which allow us to pull out a selected value to be at the top of our list without resorting to artificial characters or reliance on sorting fields, whether real or virtual.

    Trap of the Month - Tables Do Not Have an Order

    If you've ever used Excel you know that if you add a value to the bottom of a spreadsheet, the value stays at the bottom until you move it (e.g. through sorting). This is not necessarily the case with an Access table.

    You would think that the last item you entered would remain as the last item in the table, but it's not necessarily that way. If you open a table and choose to sort on a column, then close the table and respond YES, you want to save your changes, the next time you open the table it will be in the order that you last sorted on.

    Tip of the Month – Right Click for Quick Sorting

    If you want to quickly sort results in a table or query, simply right-click on the field that you want to sort on. This will reveal a pop-up menu. This menu has all sorts of goodies on it, including Sort Ascending and Sort Descending. If you select either of these, your results will immediately sort on that field.

    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
    Powered by

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