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.

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
Union
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
Union
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.

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.

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.