The Access Web - The Evils of Lookup Fields in Tables

The Access Web - The Evils of Lookup Fields in Tables

Lookup fields are a hot topic for debate, that's for sure. If you frequent Access newsgroups or discussion forums, you've probably seen at least one heated thread about whether developers should use lookup fields in their tables. Personally, I tend to agree lookup fields should NOT be used. The misunderstanding most people have regarding this stance is that they believe the functionality of lookup fields is lost. Not true.

Unless you have a *rock solid* understanding of relational theory and the detailed structure of your database, it is a very bad idea. It gets you very little - it makes your table datasheet view more human readable, at the cost of concealing the actual contents of the table, and makes sorting and searching the table more difficult; it also makes it a tiny bit easier to put combo boxes on forms instead of textboxes. But I advise people never to use this misfeature; just put the combo boxes on Forms, which is what you should be using to look at your data anyway. Table datasheets are for debugging; and as such they should display what they actually contain rather than concealing it.

You can very easily store your tables with the actual data that they contain visible, and use a Query or a combo box or other control on a Form or Report to retrieve data from the linked ("lookup", if that's what you want to call it) table. What I object to is the perverse idea that you should define a lookup *FIELD* in your main table, and use it in your table datasheet. This accomplishes absolutely nothing other than to conceal the actual contents of your table, cause confusion about the relational structure of your data, and mislead users into trying to do data entry and searching in table datasheets instead of properly doing so on Forms.

It's perfectly easy to put a Combo Box - a "lookup", if you want to call it that - on a Form. The table lookup wizard makes this the default, but at the cost of concealing the actual contents of your table; encouraging you to use table datasheets for data entry, almost never a good idea; creating a new relationship between the main table and the lookup table even if such a relationship already exists; and adding redundant indexes to your table. If you leave the tables alone, and use a Form to do your data entry, you get the best of both worlds. Use table datasheets for debugging and design ONLY, and use the Form and the rich kit of tools that the Form provides for your data entry.

For more detailed information, follow this articles link to the MVPs Access site.