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 |
Improving Software 
The MVPs really made me scared of lookup fields. I never implemented them. Maybe I should give them a try.
Excellent counterpoints!! I had stepped away from using lookup fields after I read that article too. Thanks for providing some additional insight.
sorting does NOT work
make a table called Customer.
make a table called Order that has a CustomerID as a lookup field
now query Order and sort by CustomerID
a developer gets exactly what he expects: a column sorted numerically.
A user gets something completely confusing: the Customer names are NOT sorted alphabetically. The customer sees alphabetic names in the CustomerID column, but they are NOT sorted alphabetically the way he asked.
Do NOT use look up fields
While I don’t agree with your general admonition against lookup fields, you make a good point. I’ve updated the article to reflect the sometimes wonky behavior of lookup fields.
Thanks for your input!
Here’s something strange I can’t wrap my head around. Maybe you can help.
When placing my lookup field into iif function, which appends text to the field value, I get the id value (instead of the name value). Consider.
Problem: Append text” (chair)” to Full Name field based on role field value
Solution: Use iif function to append text to field via Expression Builder
Assumption: John Doe is the committee chair
Control group: =IIf([Role]=”Chair”,[spcFullName],[spcFullName])
Result: Jane Doe, John Doe, Jack Doe
Test group: =IIf([Role]=”Chair”,[spcFullName] & ” (Chair)”,[spcFullName])
Result: Jane Doe, 2(Chair), Jack Doe
As you can see from the control group, the correct full name value is displayed regardless of whether or not the person’s role is ‘chair.’ As you can see in the test group though, appending text to the full name field (where person’s role IS ‘chair’) resulted in function printing the lookup id (not the name) value.
Any idea why the lookup field’s ID is displayed when text with appended via iif function?
Ran, field lookups are useful for display purposes only. If you want to work with the values, rather than just display them, you need to retrieve the “originals” from the relevant table.
In your example, the field spcFullName contains the foreign key, not the data itself, so if you append ” (Chair)”, you’re appending it to the key, which is why you get “2 (Chair)”.
You need to add the table that the lookup refers to (let’s say it’s called FullNames) to the query, and replace spcFullName with Fullnames.FullName. Your calculated field will then display correctly.
I have used lookup fields ever since my first database. It was a design feature that I picked up from an MS sample database. They save a lot of time when working directly with tables (which is very useful in the design stage).
They have never caused me an issue, and although I read the cited article, I happily ignored it because I recognised that none of the objections applied to my design.
For my latest project, I have done without them, as I intend to upsize it. This article is making me suspect that I have added time and complexity to the design job unnecessarily, which is rather annoying.
You make some decent points. However the overall tone seems to suggest that good database design should be user-centric as opposed to developer-centric. I, of course, don’t plan to disagree with this specifically, but I would like to point out that not all databases are – or should be – designed for the ‘user’.
For example, even in cases where an end user with no knowledge of the underlying database is the primary operator / owner of the data, a database belonging to a business may care more about business continuity than user experience.
Given that business continuity has many aspects; database portability (lookup fields are not common to all database standards), avoidance of assumptions (in your article you wrote “you have to assume that someone writing queries is not the target of the end-user abstraction provided by lookups” – this is quite some assumption for a mission-critical DB for a corporation), not to mention the fact that multiple people maintain and develop large databases owned by businesses and they may have different views on lookup data and may not assume that a given table uses such functionality – given the above, surely it’s best to avoid using lookup fields when there’s an alternative?
Okay, I know your rebuttal will be that businesses tend not to use MS Access for this very reason, and that MS Access is designed for the end user to make more simple databases rather than big business, but why encourage users (who, let’s face it, may tweak with the database as well) to get into bad habits? And why assume that your little one-man database might not eventually develop into something that *could* become mission critical one day?
I agree pretty much with hazy, as well as the assumption about the evils of lookup fields on this particular point: “Lookup fields mask what is really happening, and hide good relational methodology from the user.”
That was a big reason why it took me longer (in the beginning) to understand and implement the true relational db normalization model since it was easier to rely on the lookup fields that others created (and then I created), as a means to link data from other tables.
I’ve seen it happen to many others also, and many have gained a false sense of confidence in being able to build more advanced databases because of the lookup field link to other tables. Fortunately for me, I understood that I didn’t know enough to proceed into deeper waters until the time came.
But cheap imitation practices do delay learning correct practices, as visual conceptualization is a big part of learning and grasping true practices. Just because Johnny can drive the car around the driveway, doesn’t mean he is ready for the highway. Same goes for “relational” database theory.
Maybe someone is wondering what I am talking about. Well, for example, many dept managers gain the false belief that just because someone can present them a database that is “RELATIONAL” — has data from multiple tables being linked (RELATED) through lookup fields from other tables, that that alone proves that one understands relational db theory, when it is not the case at all.
I’ve seen it many times over, many dept Access database hobbyists come in with a supposed relational database, and thus the boss surely thinks they have arrived at the breakthrough ability that others haven’t figured out in being able to make a db relational. Now, this may sound crazy, as I am not talking about dummies here, but a dept full of engineers in various other disciplines.
Obviously, advanced users will know how to develop a db with or without lookup fields, but not everyone does. So, it is my opinion that lookup fields ought to be left for novices learning early Access database operations, and not for developmental databases.
As for the other points about lookup fields being evil, I can’t answer that, as I have not tested out those hypotheses or those rebuttals besides this one point I am making. But I know this one point to be a FACT, as I have seen a number of databases in advanced stages of cancer where chemo couldn’t even help them – all because the “lookup field” gave Johnny a license to drive when he was not ready!
Hey – NICE reply 🙂
I tend to come down on the “against” lookup fields side. Lookup fields just strike me as a shortcut designed to make it easier for novice developers to create “relational” databases. It would be much more beneficial for the developer to learn how to PROPERLY build tables and use controls on forms (such as combo boxes) to set up these types of lookups. As an advanced developer and instructor myself, I’ve run into many problems with lookup fields when it comes to working with them in VBA. They’re fine for real simple databases, but we all know how that real simple database you weren’t planning on doing much with ends up becoming the giant DB that’s now running your company after a year or two. Build it right from the start.
I believe there is merit to point 7 of “The Evils of Lookup Fields in Tables” in the case of multi-valued lookup fields (Access 2007+ database files only). I don’t like that multi valued lookups can hide the intermediary table of a many-to-many relationship.
I view lookup fields the same way I view the format & input mask property of a field- a way to define the default view of a field which saves time if you use that field on many forms and reports.
I would like the checkbox list as a UI element in Access for editing some many-to-many relationships, but I don’t want it married to a hiddent intermediary table.
Have you ever considered writing a similar post about MultiValue Fields? I have been researching MVFs for a couple days and it’s hard to find any real meat. Most things are very superficial – “they aren’t normalized, they’re bad, don’t use them” but not much depth beyond that.
BTW – I’ve used lookup fields for years and on this point I agree wholeheartedly with David Robarts. They are just an easy way to set the default interface for a field. The field stores the ID, just as they should, if you need to work with the underlying data you join it in a query.
MultiValue fields do have some real drawbacks in terms of upsizing, and insert into queries. But I’m beginning to think their benefits outweigh these concerns. In some sense they do for junction tables what lookups do for relationships, that is simplify the development at the cost of masking what is really happening – which is that Access is storing the data in a normalized table, and then hiding it.
The “insert into” problem can be handled with vba code that walks the table and uses the allowed “insert into” syntax to copy in the MVF values.
The upsizing issue seems to me to be a bit disingenous. Does it really make sense to spend extra time now coding special handling of junction tables (both for data entry and data retrieval) just to avoid spending time when (and if) this database gets upsized, which in many cases it probably wont?
Just discovered this:
http://www.utteraccess.com/forum/Multi-Fields-Evils-Lo-t1991337.html
Good article – I rely on lookup fields in a lot of my databases. eg. in one database I have it split into a front end and back end. There are 30 users accessing the database, all with front ends on their machines and a back end on the server containing the account base and lookup tables. I frequently need to update the options they have in some of the fields, which I can easily do by adding it into the lookup table in the back end on the server. If I had to change it in the combo box on the form in the front end it would mean re-installing all 30 front ends on the users machines every time I need to change something!
My databases are very much designed with the user in mind. They are completely locked down with all the tech side hidden, even the main Access back window is hidden so they look like stand-alone pieces of software.
If you’re not a VBA programmer then some of the issues mentioned would pose a problem, but a year ago I couldn’t programme VBA. Now I can, and I’ve managed to get around every obstacle I’ve come across with a little help from Google and some really helpful guys in some of the many Access forums available.
[…] of view that lookup fields are just "misunderstood" rather than being completely evil. https://improvingsoftware.com/2009/10…cess-are-evil/ Reply With […]
Spot on John,
I wrote a great app using lookup fields (indeed encountered some issues, as this is a new concept, but Internet seach resolved them all), then stumbled upon Arvin and Joan’s article and immediately disagreed with every single point.
I couldn’t agree more with your rebuttals, so thanks for publishing them to wider audience.
As a novice programmer/end-user stuck trying to maintain/improve a database someone else created 15 years ago, I absolutely hate the lookups in the table design. I am good with SQL plus, and like seeing straightforward relationships. In the Access database I am stuck with, the lookups in the tables create a snarled mess. Had the original developer created properly linked tables without the lookups, the data would have been easier to analyze and improvements easier to implement. The combo boxes would have been fine on the forms, but when trying to see the real data in the tables, the lookups are misleading. It is also frustrating to try and export data, as the exported data will not match what is shown in Access. No doubt the developer never imagined his “quick and dirty” fix would still be in use fifteen years later.
Hi All,
I have written hundreds of Access apps over the years and love it because it is a million times faster than using C# or VB to writing apps for business. I mostly use MS-SQL back end, but have built large data Access 20Gb database application using multiple Access Database to hold the table in the backend. My applications have gone well beyond the recommend user limit and have run stable for years over international virtual networks for large corporations.
More importantly I have been using lookup the feature was introduced. I have never had a problem with Lookup.
I should add that I have had many people come to me asking me to fix their Access application or been ask to fix someone else application after the left the organisation. Lookup is a common issue. I can say that often it is a result of not design the database correctly and/or configuring the Lookup Properties wrong.
I totally agree with this article. Lookup are a good thing and you should use them. If you have trouble with looks then you need to check your database design and how you have configured the lookup properties.
In fact the lookup properties are quite powerful and there are many tricks you can use to do fancy things with lookups. But using VBA and Events and ADO.
My only complaint with Access is there lack of Improvements in recent yes outside of SharePoint functionality.
I would like to say the MPV are usually on the money with their advice and this is one of the very few areas I disagree with.
I am thinking of starting a blog and doing a Lookup Training blog. If you are interested please comment. Other topic would include:
• Using Access to get and analysis data from SharePoint documents Libraries
• Using Access to build interactive and interconnect Visio charts based on data and uploading to SharePoint.
• Automated importing large amounts of data into Access
• Storing and organising with large (over 2Gb) with very large amounts of data in Access Database
• End to End building, deploying and selling an Access Application on Microsoft Office Store.
if anyone is interested drop a comment
Hey, that blog sounds like a great idea. I have a question though — When a lookup field specifies a value in another table, it populates with that value…. all well and dandy, now the field references the desired value.
But when the referenced value changes, the lookup value stays the same. If this change was someone correcting an error, I still want the lookup value to reference the corrected value. How can I link these two in a way that reflects updates to the referenced value?
The main reason most professional developers, especially those advising novices, warn against the use of lookup fields is because they mask what is really being stored. This is often confusing to new users. As an MVP, I have answered hundreds of questions over the years by novices confused by the feature and having problems with it. There are a couple of comments here that illustrate that. The original authors of the article this comments on wrote because of their own experiences dealing with questions that resulted from the confusion.
There were some comments here that confused me. Like the one that claimed they would have to update 30 front ends. Once you create a form with a lookup control that is based on a table, you don’t have to change the front end to update the table.
There are several erroneous issues in the rebuttals. Like the one in response to the upsizing issue. Also while many people develop apps only for themselves, many more develop for small workgroups. And understanding good relational design becomes important.
But the bottom line here is that, if you understand exactly how lookup fields work, you can use them effectively. But most novices don’t and encounter problems when using them.
ID can be eliminated simply in target table.
A related issue that really bothers me: Why can’t the slick multi-select combo box control that works so nicely with a lookup field be used with other types of fields? If the underlying recordsource of the combo box is not a multivalue lookup field, the checkboxes disappear and the control loses the ability to do multiple selections. In short, it becomes a dropdown list box.
I love the combo box interface, but I don’t always need a lookup field. But as it is, the combo box interface is only fully functional when used with lookup fields.
I guess I should be complaining to Microsoft.
I want to use a query to actually display the data of each field from the lookup field. ie I have a table called violations. In the violations table, it has the violation type, article number, description. I want to create a query that will display each of these fields for the given record. Then I want to merge from the query to the violation form. – that part is easy.
Access 2013 = Sorting lookup fields works just fine. As does filtering. Problem solved. 🙂
I have recently inherited a reasonable sized database and the main data table (of some 50 tables) contains over 36 lookup fields. It all works fine until…… we go to export the data into Excel. It was working seamlessly but a recent environment upgrade to Win10 with Excel 16.0 and Access 2010 (this is what we are stuck with at this point in time) has seen the lookups cause problems. Whenever we export info from this table or queries based off it, we get an error in Excel. The error doesn’t cause any data loss as it relates only to formatting but while the lookups are in place, the error is always there even if we save the export into an older Excel version. My client is trying to get me to fix this without changing the table and it’s a pain in the bum. Using a query to lookup the values from a reference table has not been successful because 36 lookups put us over the limit for the number of objects a query can be based on (I think it’s 32) and when I split the query into 2, the query was still trying to process 45 mins after executing. This is the only problem that I’ve really seen caused by lookup fields but it’s been a nightmare for me so far.