Last Updated 6 Jan 2019                         Difficulty level :   Easy

Unlike other databases, Access allows you to use lookup fields at table level.
Doing so, allows you to have combo boxes to guide end users with a list of available choices
The lookup field can be based on a table or query; value list or field list.

Although at first sight this may seem an attractive option, experienced developers normally advise strongly against their use.
Several reasons are provided in an often quoted explanation by Arvin Meyer – The Evils of Lookup Fields in Tables

The main issue is that a lookup field in a table displays the looked-up value e.g. CompanyName but actually stores a reference value such as CompanyID
The result is that any query using that lookup field to sort by company name won't work. Nor will any query using that company name for filter criteria

Lookup fields mask what is really happening and hide good relational methodology from the user.
Furthermore, the database cannot be properly upsized to another database engine such as SQL Server (without removing all the lookup fields) because no other database engines use or understand them.

A discussion thread at Access Forums.net has exposed a further reason against their use.

Exporting the table data to Excel will produce different results depending on how the export was done.
I am very grateful to regular forum contributor June7 for bringing this disparity to my attention

I have created an example database to illustrate this point :   Table Lookups  (approx 0.4 MB zipped)

This includes a table tblCars with two lookup fields:
a)   Company – based on a lookup table tblCompany
b)   Colour – based on a value list

Both lookup fields have a number datatype. Each field has a hidden bound autonumber ID column with a text description column



The data is displayed using the text description but the number value is stored in the table





There are some who argue this is a good idea as the display shows what the user needs to see whilst the number datatypes used to store the data reduce the file size.

If the data is exported to Excel the number values are exported as these are the saved values



However, if you tick the 'Export Data with formatting and layout' option before exporting, the text descriptions are exported instead!



Once again, some may argue this is a good thing in that users can select whichever output they require
However, I would disagree with that viewpoint totally due to the confusion it creates for end users

It should be remembered that end users should directly never view or edit data in tables or queries.
All data entry and editing should only be done using forms.

All lookups should therefore be applied using combo boxes or list boxes on forms.
This produces the same experience for end users but avoids all the issues with using lookup fields in tables



Colin Riddington           Mendip Data Systems                 Last Updated 6 Jan 2019