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
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
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
Select "ADD NEW" as City, 1 as SortbyMe from
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.