Are Lookup Fields Really Evil?

[Guest]

I found the warning below on an MVP site. I'm worried as I use these fileds
to establish relationships.

Any comments or advice?


The Evils of Lookup Fields in Tables
Contributors

Arvin Meyer
Joan Wild

1. 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 ID.

2. 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.

3. Another relationship is created which then creates another set of
indexes when a Lookup field is created, thus bloating the database
unnecessarily.

4. 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).

5. 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 optimized.

6. Lookup fields mask what is really happening, and hide good relational
methodology from the user.

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.

8. 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).

 

[Jeff Boyce]

Philip

You'll find (you found) a strongly-worded opinion (and consensus) that using
"lookup" data type fields in tables requires considerable attention to
detail and is fraught with opportunities (to mess up).

Lookup tables, referenced via combo boxes (and list boxes) in forms is the
preferred method of handling lookups (when you are not using a simple value
list in the form's combo box).

Is there something about the caution that you disagree with?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Joan Wild]

Hi Philip, as long as you understand all the shortcomings and know how to
work with them, you are fine.

It's just that most beginning Access users get very confused by lookup
fields. Also that page is referring specifically to using the lookup wizard
in a table.

It adds hidden (and often duplicate) indexes and relationships. You can
create your own relationships and set referential integrity (which the
lookup wizard does not do) in the relationships window.

If you like the fact that a combobox is so easily created on a form based on
a lookup field, you can create the lookup field yourself (not using the
lookup wizard), and no hidden index/relationship will be created.

Or better still, is to create the lookup in a query - check out the Lookup
tab in the properties of a column in a query. Again no hidden
index/relationship.

 

[Guest]

Joan

Thanks for the quick response. All of this is driven my confusion regarding
setting up relationships. I see that when I use a lookup field the two
tables are linked in the relationships window. Is this link the
"relationship", or does it only become a relationship editing through the
relationships dialog box?
--
Regards

Philip

Hi Philip, as long as you understand all the shortcomings and know how to
work with them, you are fine.

It's just that most beginning Access users get very confused by lookup
fields. Also that page is referring specifically to using the lookup wizard
in a table.

It adds hidden (and often duplicate) indexes and relationships. You can
create your own relationships and set referential integrity (which the
lookup wizard does not do) in the relationships window.

If you like the fact that a combobox is so easily created on a form based on
a lookup field, you can create the lookup field yourself (not using the
lookup wizard), and no hidden index/relationship will be created.

Or better still, is to create the lookup in a query - check out the Lookup
tab in the properties of a column in a query. Again no hidden
index/relationship.

 

[Guest]

Hi Jeff

I appreciate your quick reply.

The fact is I'm not experienced to strongly disagree with anything.

Regards

Philip

Philip

You'll find (you found) a strongly-worded opinion (and consensus) that using
"lookup" data type fields in tables requires considerable attention to
detail and is fraught with opportunities (to mess up).

Lookup tables, referenced via combo boxes (and list boxes) in forms is the
preferred method of handling lookups (when you are not using a simple value
list in the form's combo box).

Is there something about the caution that you disagree with?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Douglas J Steele]

Yes, that's a relationship, but it's not the only (nor the best) way to
create a relationship.

The best way to create a relationship is to open the Relationships window
(look under the Tools menu) and explicitly create the relationship by
dragging a field (or fields) from one table to the other.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Joan

Thanks for the quick response. All of this is driven my confusion regarding
setting up relationships. I see that when I use a lookup field the two
tables are linked in the relationships window. Is this link the
"relationship", or does it only become a relationship editing through the
relationships dialog box?

 

[Joan Wild]

Yes that is a relationship, but you'll see it doesn't enforce referential
integrity. It's just a plain join between the tables. If you double-click
the join line, you'll see a properties box where you can set 'enforce
referential'.

This means that a record must exist in the parent table, before a child
record can be created.

I can recall one of my early databases where I used lookup fields on every
foreign key field, for the express purpose of having Access create the
relationships. However, I then had to edit each relationship to enforce
referential integrity (and only later learned about the hidden indexes which
were duplicates of ones I had already created).