Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Updated 06/01/2019

Issues with Table Level Lookup Fields

Return to Home Page

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 against their use.

Several reasons are provided in an often quoted explanation by Arvin MeyerThe 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 engines use or understand them.

 

 

Colin Riddington      Mendip Data Systems        06/01/2019

Return to Top
* Required

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

Return to Access Articles

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:

•  Company – based on a lookup table tblCompany

•  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

TableLookupsDesign TableLookupsForm TablelookupsData TableLookupExcelExport2 TableLookupExcelExport1

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

 

Click any image to view a larger version

Difficulty level :   Easy