Where do Lookup Fields in Tables Come From Anyway?Whether you consider them to be a useful idea or not, lookup fields in Access tables came from somewhere and we have to deal with them. My candidate for their dubious origin is SharePoint, but I have only a hunch. I know they were already in Access 2003, but I no longer have earlier versions of Access available and can't recall prior to that. (I'd appreciate being set straight on this point, if anyone knows more about it.) Suffice it say they made their way past the door wardens and are now firmly ensconced in MS Access tables, alongside the other dodgy characters cadging drinks from the regular patrons and whistling at the wait staff as they squeeze past to deliver the next round to the table behind.
We're not talking about combo boxes, or drop downs, on formsI want to be clear before we get into this in depth.
We are talking about lookup fields in tables. Combo boxes on forms work almost exactly the same way in some regards. They can also be abused in the some of the same ways. It's just that the more pernicious implementations I've seen were born in tables.
What do Lookup Fields in Tables Do?The basic concept of a lookup field sounds pretty good -- at first look. Make it easy for users to see the values, not the foreign keys, for fields. You have probably had the experience of opening a table and seeing columns of foreign key values in one or more fields. Trying to equate what you see there, e.g. CompanyID of 210, with the value to which that key value relates, i.e. "The Big Box Company", which is CompanyID 210 in tblCompany.
By converting the foreign key field to a lookup field, you magically allow users to see "The Big Box Company" even though the table stores Foreign Key 210.
Done right, and fully understood, they can be benign and even handy for a developer. So far, so good. But like so many of life's little problems, it doesn't end there.
What Could POSSIBLY GO Wrong?Actually, more than you might expect.
Here's an example of one of my least favorite attempts to use Look up fields in tables.
Storing the Value, not the Key.This is the most common mistake I see. The best way to get to the point is with some images. So, here's a typical Static Lookup Table. This table has a Primary Key and a Value field. Many Lookup Tables are like this.
|Lookup Table of Months|
SELECT DateValue([WD].[WorkDate]) AS WD
FROM tblWorkDetail AS WD
GROUP BY DateValue([WD].[WorkDate])
ORDER BY DateValue([WD].[WorkDate]) DESC;
tblWorkDetail contains several other fields, including the start and stop times for the work, a description of the work, and the WorkID itself to which that detail relates. To select all work by date completed for an invoice, this list needs only the relevant dates, grouped on Date. Selecting one date from the combo box adds all of the work for that date to an invoice. Double duty. But I digress. Back to lookup tables which have been infected with Lookup Fields.
Different considerations apply to static and dynamic lookup tables. With a Static list, like Months, it's fine to dispense with the surrogate key. I've seen many tables like this that work just fine.
|Static Lookup Table with a Single field|
With a Dynamic list, like Vendors, the issues are different. What happens, for example, if you misspell a name as "The Doctar Company" instead of "The Doctor Company". After a week of data entry someone finally notices the misspelling. By now there are multiple purchases made from the company. How hard is it going to be to correct the spelling mistake?
If your Vendors Lookup table is like the first example, of course, all of those related records were stored in the purchase table under a surrogate key, (i.e. the Primary Key of the Vendor table.) No problem, then, correct the Value field in the vendor table and you're done. The related records are not impacted at all.
|Surrogate Primary Key to Foreign Key|
If your Vendors table is like the second example, it gets a bit stickier. First, you may or may not have defined the parent-child relationship with Cascade Updates. If so, changing the spelling in the lookup table should cascade the correction to all of the related records child records. Quite doable and safe enough, if not really ideal.
|Cascade Update Saves The Day With a Single Value Lookup Table|
The long way round to the point, but here we are at lastWith that background on the basics of relationships and lookup tables, let's go back to the one in our original table. It looks like this in design view.
|Defining a Lookup Field in a Table|
And it looks like this in datasheet view.
|Resulting "Lookup List|
In fact, on the surface, there's no way to differentiate the two in datasheet view. Consider these four screenshots.
|Datasheet View of Lookup fields in tables, closed and dropped|
It's not surprising then, that many times newcomers are confused when they try to work with such tables in their queries. "Why do I see the Names when I look at the table, but when I use them in a query, get the numbers instead?"
So, now, here are some queries based on these tables.
|Query Using Tables with Lookup Field based on Surrogate Key|
|Valid Filtering on Lookup Field using Foreign Key|
Now try this.
|Invalid Filtering on On lookup Key using visible, Value Field|
Is it any wonder that so many newcomers flounder when they find that these sweet, sweet lookup fields in their tables are laden with hidden pitfalls?
And it gets worse from here. I promise.
Come back later for a followup on other problems I've seen.