Evils of Lookup Fields in Tables
- A Lookup field in a table displays
the looked-up value. For instance, if a user opens a table
datasheet 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
- Any query that uses that lookup
field to sort by that company name won't work. Nor will a
query that uses a company name in that field as a criteria.
If a user creates a combobox to select the company using a
value list, the data in the table can be over-written.
- Another relationship is created
which then creates another set of indexes when a Lookup
field is created, thus bloating the database unnecessarily.
- If a combobox 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).
- Reports based on the lookup field
need a combobox to display the data, causing them to run
more slowly. The underlying recordsource can also be
modified to include the table, however the index, (unless it
was set up within a proper relationship) may not be
- Lookup fields mask what is really
happening, and hide good relational methodology from the
- The database cannot be properly
upsized to, or queried by, another engine (without removing
all the lookup fields) because no other engines use or
- If security is implemented,
permissions to tables is usually denied, and RWOP 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).