A comment on one of my answers on StackOverflow brought to my attention a blog article “The Evils of Lookup Fields in Tables” that argues against defining lookup fields in MS Access table definitions. Quite interesting. I don’t do nearly as much Access development as I once did, but I had never encountered any serious issues using Lookup fields.
The article is written by Arvin Meyer and Joan Wild, who appear to be part of the Microsoft MVP program. My experience predisposes me to give a lot of credibility to MVPs even when I am initially skeptical of something they say. However, given the full context of their complete argument. I am calling foul on this one.
Lookup Fields in Access
First, a quick primer on this handy feature of MS Access for anyone that is not familiar with it.
In the design view for a table definition, you can define a display control type for a each field that is used to display it in data-sheet view and also used as a default when the field is added to a form. “Lookup field” in this context of this article and the one it responds to simply refers to a field defined with a “Combo Box” display control that uses a table or query as the row source.

The effect of using a lookup field for the configuration shown above is that the user gets a handy drop-down control in data-sheet view as shown below. In this example, the Company field is actually a numeric foreign key to the companies table, but the user gets to pick a company based on a CompanyName instead of the CompanyID. The CompanyID is still stored in the underlying table.

In fact, by changing the configuration slightly, you can even make it display BOTH the CompanyID and CompanyName in the drop-down.

The great thing about this is that it removes one of the excuses novice developers often make for not normalizing their databases sufficiently.
The users don’t want to have to look up the CompanyID to enter a new customer! It’s just easier to put all the company information in the customers table.
-Novice Developer
When you consider that Access is the tool of choice for a lot of novice developers, removing obstacles to good DB design was a good strategy for the Access product team at Microsoft.
Dubious Claims
So back to the assertion that this feature should be avoided. Let’s tackle the claims one at a time.
1. A Lookup field in a table displays the looked-up value. For instance, if a user opens a table data-sheet and sees a column of company names, what is in the table is, in fact, a numeric CompanyID, and the table is linked with a select statement to the company table by that ID.
Rebuttal: Yes, this is exactly the point of this feature and why it is beneficial.
2a. Any query that uses that lookup field to sort by that company name won’t work.
Rebuttal: Sorting does indeed work. It sorts on the first displayed column in the lookup control and not necessarily the underlying value. It is much more intuitive to the user to have it sort based on what is displayed rather than a hidden underlying value that you are trying to hide from them in the first place. In any case, how often do you want to sort on a numeric foreign key value anyway? Retracted. I still take issue that sorting in a query on a lookup field does work correctly, but I can see that the original author may have been talking about using the sort feature in table/form view which is admittedly a little wonky. I’ll offer the benefit of the doubt on this one, but would like to see clarification in the original article.
2b. Nor will a query that uses a company name in that field as a criteria.
Rebuttal: When writing queries, you do have to specify the criteria using the underlying value rather than the user-friendly displayed value. However, you have to assume that someone writing queries is not the target of the end-user abstraction provided by lookups and will know to supply a numeric criteria for “CompanyID.”
2c. If a user creates a combo box to select the company using a value list, the data in the table can be over-written.
Rebuttal: First, one of the reasons for this feature is that it auto-creates the combo-box with the appropriate settings. Although, I have no idea what they mean by “the data in the table can be over-written”, it is true that someone could modify the form design to do things that mess up the data. This is not unique to any specific feature.
3. Another relationship is created which then creates another set of indexes when a Lookup field is created, thus bloating the database unnecessarily.
Rebuttal: You shouldn’t use a feature that simplifies life for the developer and end user because it might bloat the database? You weren’t planning on adding a relationship and/or indexes to your foreign and primary keys anyway?
4. If a combo box based on the lookup is used in a form, and a filter is applied, the persistent filter effect of Access often saves the filter and the next time the form is opened, there will be a prompt for the value (which cannot be provided, thus creating an error).
Rebuttal: I tested this one out in thoroughly and could not reproduce what this person is describing. I have seen similar behavior in Access before even without lookup fields. It seems unrelated.
5. Reports based on the lookup field need a combo box to display the data, causing them to run more slowly. The underlying record source can also be modified to include the table, however the index, (unless it was set up within a proper relationship) may not be optimized.
Rebuttal: Uhhh. Yeah. If you want to display a value from a related table, the report is going to need to pull it from that other table combo-box or not. Assuming that the combo-box method is slower than adding a join to the underlying query, so what? Adding a lookup to a field doesn’t REQUIRE you to have a combo-box, and not having a lookup field doesn’t stop you from using a combo box.
6. Lookup fields mask what is really happening, and hide good relational methodology from the user.
Rebuttal: The point of your app is not to teach the user about “good relational methodology.” As much as you’d like them to care about the intricacies of your very important job, they just want a way to efficiently work with their data. In fact, this feature encourages the use of good database design by giving the developer a way to hide the technical details behind the scenes.
7. The database cannot be properly upsized to, or queried by, another engine (without removing all the lookup fields) because no other engines use or understand them.
Rebuttal: Wrong, Wrong, Wrong. Other databases might not support this particular feature to display the data automatically with the cool drop downs, but they can use these fields just like any other. The upsizing thing is irrelevant, despite the fact that you configure this in the table design, it is a UI feature not a data format.
8. If security is implemented, permissions to tables[are] usually denied, and [Run with owner’s permissions] queries are used for data access. There will often be errors that there are no permissions on a specific table that isn’t even being used in a query (because the lookup field is). If the queries are nested or complex, it can take some time to track down the lookup that’s causing the error (that is, if it occurs to you).
Rebuttal: So the argument here is that if you set up complicated security, this feature will trip you up because you have to remember to give permissions on the related table. I suppose you don’t have to worry about permissions on the related table if you forgo this feature and manually add that table to the query? Is it really that confusing if you get an error telling you it doesn’t have permissions on {specific table} just because it isn’t explicitly in the query? Sounds like a PICNIC problem.
Update:
As pointed out in the comments (thanks JamesL), in my rebuttal to the weak arguments against using lookup fields, I was remiss in not mentioning that there are some legitimate complaints about the implementation of this feature in MS-Access. I still don’t think the feature should be discarded completely, but it definitely needs to be used with an understanding of its quirks and shortcomings.
The problem with lookup fields
Using the built in sorting and filtering features provided by access in form and table view against a lookup field that is configured to display a value other than the underlying value (as shown in my examples above) is definitely wonky and can be confusing to users. The implementation is textbook case of a leaky abstraction.
Note: I did some experimentation and found that different versions of Access misbehaved in slightly different ways.
Problem 1: The sort feature in the UI (at least in Access 2007) sorts based on the underlying field value regardless of what is displayed. This could definitely be confusing to users.
Problem 2: The right-click filter-by-example feature is REALLY misleading and flat out broken. The filtering options are based on the displayed value, but do nothing when you click them because they don’t match up with the underlying data-type of the field (usually). In Access 2007, this feature simply ignores your click when you try to use the feature whereas in earlier versions of Access it would throw a type mismatch error.

Oops!
Thanks again to JamesL for pointing out the omission/error in the original article. I definitely want to make sure I am providing accurate and useful information in this blog, and rely on the watchful eye of my readers to keep me in line.
Filed under: Technology Tips | 25 Comments »