Using A Foreign Key's Value in a Table's OrderBy Property

[twinnyfo]

Here is a handy trick you might have a need for as you expand your projects. Forgive the length, as it can get complex. However, the solution is simple!

I have a Table, we will call it tblPeople:

Field     Type        Description
ID        AutoNumber  PK
FullName  Text        Person's Name (Last, First MI)
Other fields, etc.

Properly Normalized, all that jazz.... Because FullName is one of the Text Fields, we can sort by the person's Name (should we so desire), and many times we do, depending on what we are doing. It is usually in the Order By somewhere, whenever we pull data including the name. The FullName is spit out by the MDSS (Master Data System in the Sky) and we have no control over it, so it is what it is, and since it begins with the Last Name first, it is good for sorting.

SOME (I say again, SOME) of these people have circumstances which pertain only to them. So, for proper DB normalization, we have a separate table for just these people. We will call it tblGoodPeople:

Field   Type        Description
ID      AutoNumber  PK
Person  Long        FK to tblPeople
Other fields, etc.

Now, if you were to have tblGoodPeople's OrderBy Property set to [tblGoodPeople].[Person], because this field is a long integer, it will sort the records based on the value of the FK. So, your names may not be sorted properly (so you could actually find someone easily if you had to look at the table).

In tblGoodPeople, I use a LookUp field, RowSourceType = Table/Query and RowSource =

SELECT tblPeople.ID, tblPeople.FullName
FROM tblPeople
ORDER BY tblPeople.FullName;

By doing this, the Person field in tblGoodPeople is now displayed as the person's name. Again, if the OrderBy Property is set to Person, it will still sort by the numerical value of the field.

However, there is a neat feature available when you have foreign keys and you have a lookup field. Here is an example.

All you have to do is use the Prefix "Lookup_" and indicate the Lookup Field that you want to use (assuming you had multiple fields to sort on). Thus, I set my OrderBy Property to:

Lookup_Person.FullName

because the Field Person could have the values of either ID or FullName.

And my table now sorts by the person's name!

There is nothing ground-breaking in this insight and many of the Experts here may have already known about this trick. However, for some of the younger Jedis, this little secret about the Force is given to help out.

Enjoy this little tidbit of MS Access trickery.....