Lookup fields in tables bad?

Good day all,

I read recently (having followed some links to various Access related
websites from posts here) that one should avoid altogether the use of lookup
fields in tables.

Though I am indeed a novice at this, I have created a number of simple
databases over the years, and in my limited understanding thought lookup
fields were one of the benefits of a relational database? So, I created lots
of lookup fields in my tables...lots and lots of them.

My question is two-fold. First; what is the risk and what bad things can
happen to the wayward soul who unwittingly builds lookup fields into his
tables, and second; if such practice should be avoided like the plague, how
does one go about eliminating them after the fact?

Maybe one more question; I also use field lists and value lists at the table
level. Is this the same, and just as bad as having an SQL lookup field in a
table?

In a copy of a database I’m working on now, I changed all of the table level
lookup fields (SQL type) to straight text boxes and I’m waiting now for that
copy to implode…but it hasn’t yet, and all of my forms are working correctly.
(I have lots and lots of lookups as the Row Source property.)

Since I changed the table fields to Text Boxes, I haven’t tried to enter any
new data…maybe Armageddon awaits me there?

I rarely enter data directly into my tables; and even when I do it's only
after a first pass at building one, and just before I create a form for data
entry purposes. Since my forms all have lookup fields in them, will my house
of cards tumble only if I were to enter data directly into a table?

If anyone has some good insight on this, or would simply to poke fun at me
for my ignorance or even just offer anecdotal comments on what may be my
impending doom, I would appreciate any input at all.

Thank you in advance.

Best regards,
RL

On Thu, 22 Jan 2009 17:04:03 -0800, Robert5833 wrote:

Good day all,

I read recently (having followed some links to various Access related
websites from posts here) that one should avoid altogether the use of
lookup fields in tables.

Though I am indeed a novice at this, I have created a number of simple
databases over the years, and in my limited understanding thought lookup
fields were one of the benefits of a relational database? So, I created
lots of lookup fields in my tables...lots and lots of them.

All of the advantages of lookup fields in tables are only there if you
work directly with tables. Nobody who develops serious apps for people
create those apps so users can work directly in tables.

They use forms and reports for the interface and when you do that lookup
fields in tables lose all their advantages and pick up a few
disadvantages. The most notable is that your tables display data that is
not really there which can be confusing when using them in queries and
forms and reports.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Okay, lets see if we can clarify things for you here.

In table design, if you choose a field of type "Lookup Wizard", you fire up
a wizard that many of us don't like. We tend to avoid this wizard because of
its side effects. You have probably read this:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

But no one is saying that you should not use foreign keys. Every non-trivial
database has lookup tables -- small tables that contain the valid values for
a field in a table that looks them up. That is the only way to correctly
normalize your data.

There is a debate about whether the lookup table should have a numeric field
as its primary key (in which case your foreign key field will also be a
Number), or whether you should use a natural key (Text type.) If you have a
lookup table for categories, each category name will be unique. If they are
also relatively brief (just a couple of dozen characters), it makes sense to
me to use that as the natural key instead of introducing an artificial key
(such as an AutoNumber.) Sounds like you may have done that in some cases.

Next the question arises as to whether your main table should use a text box
or a combo box for the foreign key field. Combos are great on forms, but
many developers avoid them in the table because it masks what data is really
here. For example, if you use the Lookup Wizard, you get a field of type
Number, but it displays the text. That's confusing for developers and
newbies alike: you actually have to open the table in design view in order
to build a WHERE clause on the field, because you don't know what delimiters
you need just by looking at the data.

You also raise the possibility of using value lists rather than a lookup
table. IMHO, that's the worst possible solution. It's trivial to let the
user add more records to a lookup table, but modifying the items in a lookup
list requires design changes. For more info on this, see:
http://allenbrowne.com/ser-27.html#ValueList

So, FWIW, my recommendations would be:
- Do use foreign keys.
- Avoid the Lookup wizard.
- Use natural keys where appropriate.
- Use combos in your table only rarely.
- A Value List in a combo in a table is too inflexible.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert5833" wrote in message
...
Good day all,

I read recently (having followed some links to various Access related
websites from posts here) that one should avoid altogether the use of
lookup
fields in tables.

Though I am indeed a novice at this, I have created a number of simple
databases over the years, and in my limited understanding thought lookup
fields were one of the benefits of a relational database? So, I created
lots
of lookup fields in my tables...lots and lots of them.

My question is two-fold. First; what is the risk and what bad things can
happen to the wayward soul who unwittingly builds lookup fields into his
tables, and second; if such practice should be avoided like the plague,
how
does one go about eliminating them after the fact?

Maybe one more question; I also use field lists and value lists at the
table
level. Is this the same, and just as bad as having an SQL lookup field in
a
table?

In a copy of a database I’m working on now, I changed all of the table
level
lookup fields (SQL type) to straight text boxes and I’m waiting now for
that
copy to implode…but it hasn’t yet, and all of my forms are working
correctly.
(I have lots and lots of lookups as the Row Source property.)

Since I changed the table fields to Text Boxes, I haven’t tried to enter
any
new data…maybe Armageddon awaits me there?

I rarely enter data directly into my tables; and even when I do it's only
after a first pass at building one, and just before I create a form for
data
entry purposes. Since my forms all have lookup fields in them, will my
house
of cards tumble only if I were to enter data directly into a table?

If anyone has some good insight on this, or would simply to poke fun at me
for my ignorance or even just offer anecdotal comments on what may be my
impending doom, I would appreciate any input at all.

Thank you in advance.

Best regards,
RL

"Allen Browne" wrote in
:

here is a debate about whether the lookup table should have a
numeric field as its primary key (in which case your foreign key
field will also be a Number), or whether you should use a natural
key (Text type.) If you have a lookup table for categories, each
category name will be unique. If they are also relatively brief
(just a couple of dozen characters), it makes sense to me to use
that as the natural key instead of introducing an artificial key
(such as an AutoNumber.)

I would add to all those things the question of whether the values
are ever going to be updated. If they can change, then I'd use an
Autonumber surrogate key, instead of the natural key.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Thank you Rick! Great info! I'll post a reply to all of sorts...

Robert

"Rick Brandt" wrote:

On Thu, 22 Jan 2009 17:04:03 -0800, Robert5833 wrote:

Good day all,

I read recently (having followed some links to various Access related
websites from posts here) that one should avoid altogether the use of
lookup fields in tables.

Though I am indeed a novice at this, I have created a number of simple
databases over the years, and in my limited understanding thought lookup
fields were one of the benefits of a relational database? So, I created
lots of lookup fields in my tables...lots and lots of them.

All of the advantages of lookup fields in tables are only there if you
work directly with tables. Nobody who develops serious apps for people
create those apps so users can work directly in tables.

They use forms and reports for the interface and when you do that lookup
fields in tables lose all their advantages and pick up a few
disadvantages. The most notable is that your tables display data that is
not really there which can be confusing when using them in queries and
forms and reports.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hi Allen,
Thanks so much for the great outline on this subject, and things are much
clearer to me now.

In fact I have frequented your site and others, trying to gain what
knowledge I can from the pros, and I spend hours reading the helpful posts
here, and those interesting and helpful resources you and others make
available to guys like me who are just beginning.

And I thank everyone in this group for their generosity, and their
willingness and enthusiasm to help others. These discussion groups have been
an invaluable resource for me.

Back to the conversation at hand; as for primary and foreign keys, that
hasn’t been too much of a struggle for me, but I’ve been less apt to use a
natural key and almost always go with the Autonumber variety. But given your
input I’m beginning to see that the options are there, although it will take
me a while to understand when one is more desirable than the other.

My bigger struggles have been with all of the other lookup fields that I
build into my tables that over time I began to realize didn’t add any value
at all (I do use forms exclusively for data entry, and all of that
functionality is in them as well). Not to mention (and as you and others have
pointed out) the problems that result in queries and code when the field
properties aren’t what one thinks they might be given the data they display.

My overuse of lookup fields in tables seems to be a carryover from a time
when I didn’t know how to use forms well enough, and didn’t know how to build
the power of lookup fields and controls into them.

That and the fact that the Lookup Field Wizard was just so handy and easy to
use… And interestingly, for all of the hours I have spent reading the
material on the MS Access DN site, not a single mention that I can recall of
the pitfalls?

I’d like to think that I only use lookup fields in tables when they are
foreign key references, but just given the quantity of them in my tables I’m
not sure I understand when a foreign key could be a text box, or when it must
be a combo box with lookup? (I would be grateful if you or someone could
clarify that point for me; what is the rule, logic, or rationale for one or
the other.)

With regard to your comments and those of others about the use of Value
Lists, Field Lists, List Boxes and the like, I’m much clearer now on their
limitations. While I have used them on occasion, it has only been when I have
a short list of selections, and only when I know those selections aren’t
likely to change. When I do use them, I incorporate the Limit to List
functions to keep them from growing. But I have found it difficult to
manipulate or process data with them, so I have for the most part left them
behind opting for an additional table instead and now I may eliminate them
altogether.

To be sure, I have a long, long way to go; but I look forward to reading
more of your writings and the writings of others on this and other topics.

I don’t want to abuse or monopolize anyone’s time here, so I’ll go back to
reading everything I can find on this subject, and post again if I have any
specific questions.

Thanks again for the post!

Best regards,
Robert


"Allen Browne" wrote:

Okay, lets see if we can clarify things for you here.

In table design, if you choose a field of type "Lookup Wizard", you fire up
a wizard that many of us don't like. We tend to avoid this wizard because of
its side effects. You have probably read this:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

But no one is saying that you should not use foreign keys. Every non-trivial
database has lookup tables -- small tables that contain the valid values for
a field in a table that looks them up. That is the only way to correctly
normalize your data.

There is a debate about whether the lookup table should have a numeric field
as its primary key (in which case your foreign key field will also be a
Number), or whether you should use a natural key (Text type.) If you have a
lookup table for categories, each category name will be unique. If they are
also relatively brief (just a couple of dozen characters), it makes sense to
me to use that as the natural key instead of introducing an artificial key
(such as an AutoNumber.) Sounds like you may have done that in some cases.

Next the question arises as to whether your main table should use a text box
or a combo box for the foreign key field. Combos are great on forms, but
many developers avoid them in the table because it masks what data is really
here. For example, if you use the Lookup Wizard, you get a field of type
Number, but it displays the text. That's confusing for developers and
newbies alike: you actually have to open the table in design view in order
to build a WHERE clause on the field, because you don't know what delimiters
you need just by looking at the data.

You also raise the possibility of using value lists rather than a lookup
table. IMHO, that's the worst possible solution. It's trivial to let the
user add more records to a lookup table, but modifying the items in a lookup
list requires design changes. For more info on this, see:
http://allenbrowne.com/ser-27.html#ValueList

So, FWIW, my recommendations would be:
- Do use foreign keys.
- Avoid the Lookup wizard.
- Use natural keys where appropriate.
- Use combos in your table only rarely.
- A Value List in a combo in a table is too inflexible.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert5833" wrote in message
...
Good day all,

I read recently (having followed some links to various Access related
websites from posts here) that one should avoid altogether the use of
lookup
fields in tables.

Though I am indeed a novice at this, I have created a number of simple
databases over the years, and in my limited understanding thought lookup
fields were one of the benefits of a relational database? So, I created
lots
of lookup fields in my tables...lots and lots of them.

My question is two-fold. First; what is the risk and what bad things can
happen to the wayward soul who unwittingly builds lookup fields into his
tables, and second; if such practice should be avoided like the plague,
how
does one go about eliminating them after the fact?

Maybe one more question; I also use field lists and value lists at the
table
level. Is this the same, and just as bad as having an SQL lookup field in
a
table?

In a copy of a database I’m working on now, I changed all of the table
level
lookup fields (SQL type) to straight text boxes and I’m waiting now for
that
copy to implode…but it hasn’t yet, and all of my forms are working
correctly.
(I have lots and lots of lookups as the Row Source property.)

Since I changed the table fields to Text Boxes, I haven’t tried to enter
any
new data…maybe Armageddon awaits me there?

I rarely enter data directly into my tables; and even when I do it's only
after a first pass at building one, and just before I create a form for
data
entry purposes. Since my forms all have lookup fields in them, will my
house
of cards tumble only if I were to enter data directly into a table?

If anyone has some good insight on this, or would simply to poke fun at me
for my ignorance or even just offer anecdotal comments on what may be my
impending doom, I would appreciate any input at all.

Thank you in advance.

Best regards,
RL

Hi David,

Thank you for the reply. I use almost exclusively the Autonumber surrogate
key; maybe to a fault? On occasion I find that a table with just a few
records might lend itself to use of a natural key, but I don't know enough
about what I can, or cannot do with the data later, so to be safe I opt for
the Autonumber.

I suppose as time goes by and I gain a lot more knowledge than I have today,
that point or question will resolve itself.

Thanks again for the post!

Best regards,
Robert


"David W. Fenton" wrote:

"Allen Browne" wrote in
:

here is a debate about whether the lookup table should have a
numeric field as its primary key (in which case your foreign key
field will also be a Number), or whether you should use a natural
key (Text type.) If you have a lookup table for categories, each
category name will be unique. If they are also relatively brief
(just a couple of dozen characters), it makes sense to me to use
that as the natural key instead of introducing an artificial key
(such as an AutoNumber.)

I would add to all those things the question of whether the values
are ever going to be updated. If they can change, then I'd use an
Autonumber surrogate key, instead of the natural key.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On Thu, 22 Jan 2009 20:33:01 -0800, Robert5833
wrote:

I’d like to think that I only use lookup fields in tables when they are
foreign key references, but just given the quantity of them in my tables I’m
not sure I understand when a foreign key could be a text box, or when it must
be a combo box with lookup? (I would be grateful if you or someone could
clarify that point for me; what is the rule, logic, or rationale for one or
the other.)

A foreign key can *never* be a textbox, nor can it ever be a combo box!

Textboxes and combo boxes are display tools. Keys (primary or foreign) are
fields in a Table. Those fields can be displayed - on a form or report, or
thanks to some questionable design decisions by the Access development team,
in tables - but don't confuse the data *storage* with the data *presentation*.
Primary and foreign keys are logical entities defining the relationships
between data.

A Primary Key is a field - or a set of up to ten fields - which uniquely
identify a record in a table.

A Foreign Key is a field - or a set of up to ten fields - containing the same
data as the Primary Key of some other table, and serving as a link to that
table.

Any field (whether it's a primary key, a foreign key, or not used as a key at
all) can be displayed using the various data display tools that Access
provides - or for that matter, not displayed at all. For instance, if I use an
autonumber primary key (which I do), it will NOT be displayed to the user at
all; the same applies to the Long Integer foreign keys which are related to
it.

The natural key/surrogate key argument can get long and heated. I'll use
natural keys when they're short, stable, and unique, which actually leaves out
a lot of fields that otherwise might be candidates. People's names, for
example, fail on all three counts! Some examples where natural keys may be
appropriate are State two-letter codes (if your addresses are entirely or
mostly in the United States and Canada); there's only one state/province with
ID as a code, two letters is suitably short, and they rarely change (Canada
added NU and NT, Nunavut and Northwest Territory, a few years ago); five digit
ZIP codes are another reasonable choice.
--

John W. Vinson [MVP]

Natural verses artificial keys
=====================
You'll find diverse views on this, and I encourage you to read contrary
views to get a balanced perspective.

I personally find natural keys handy for several reasons:

a) If you are going to require and uniquely index a single field in a lookup
table (such as a category or type), I see no point in adding another
artificial key as well. Keep it as simple as possible.

b) The foreign key (i.e the field in the other table that looks up this Text
field) is also Text. It's humanly meaningful, and sometimes reduces the
number of additional lookup tables you need in your queries.

c) The interface is easier/more flexible, avoiding several problems that
occur if a combo's display column is not the bound column. For example:

- Combo doesn't go blank in datasheet/continuous form view if you filter its
RowSource.

- Filters based on the combo are less problematic (and differ based on
version of Access.)

- Limit to List is optional (where you don't enforce referential integrity,
or where the combo's RowSource is filtered.)

- All versions of Access have display bugs where you filter a combo's
RowSource (e.g. in Form_Current.) You avoid these problems if the bound
column is the display column.

In summary, using a simple, short, required, unique text field as a natural
key is a simpler design, and avoids some limitations, complexities, and bugs
in the Access interface.

Cascading updates/deletes
=====================
If you do decide to use natural keys, the question arises about whether you
should use cascading updates. Again, informed people like Tony Toews and
David Fenton don't like them and you've been referred to Tony's page
explaining why.

I do use them (sometimes.) If a category has been misspelled, it makes sense
to me to allow the user to correct the spelling in the form where they
manage the lookup values, and have JET cascade the update to the related
tables.

I almost never use cascading deletes on this kind of data. You really don't
want all the records removed from your main table just because some idiot
thought the category was no longer useful and decided to delete it. On
occasion, I will use cascade-to-null. This means that the foreign key is set
to Null in the related table when you delete the category from the lookup
table, so the records are not lost: they become uncategorized. If you want
to read more about that, see:
Cascade to Null Relations
at:
http://allenbrowne.com/ser-64.html

Overusing lookups
==============
Not sure what examples you have in mind. All my databases have numerous
lookup tables (typically between 5 and 40, depending on the complexity of
the database.)

The only cases where I don't bother creating a lookup table is where the
choices are extremely limited and set in concrete, such as "M"/"F"/Null for
Gender, or True/False/Null (using a Number field), of a field that holds a
number represnting a data type (values for dbText, dbLong, etc.)

Interfacing fields
============
John has explained the view that tables are to hold fields so combos don't
belong there at all. That's a rule I follow about 90% of the time, but I
sometimes use a combo in the table - mostly for the examples above where the
RowSource is a Value List.

Some of this stuff you decide by experience.
Have a go at different approaches, and see what works for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert5833" wrote in message
...
Hi Allen,
Thanks so much for the great outline on this subject, and things are much
clearer to me now.

In fact I have frequented your site and others, trying to gain what
knowledge I can from the pros, and I spend hours reading the helpful posts
here, and those interesting and helpful resources you and others make
available to guys like me who are just beginning.

And I thank everyone in this group for their generosity, and their
willingness and enthusiasm to help others. These discussion groups have
been
an invaluable resource for me.

Back to the conversation at hand; as for primary and foreign keys, that
hasn’t been too much of a struggle for me, but I’ve been less apt to use a
natural key and almost always go with the Autonumber variety. But given
your
input I’m beginning to see that the options are there, although it will
take
me a while to understand when one is more desirable than the other.

My bigger struggles have been with all of the other lookup fields that I
build into my tables that over time I began to realize didn’t add any
value
at all (I do use forms exclusively for data entry, and all of that
functionality is in them as well). Not to mention (and as you and others
have
pointed out) the problems that result in queries and code when the field
properties aren’t what one thinks they might be given the data they
display.

My overuse of lookup fields in tables seems to be a carryover from a time
when I didn’t know how to use forms well enough, and didn’t know how to
build
the power of lookup fields and controls into them.

That and the fact that the Lookup Field Wizard was just so handy and easy
to
use… And interestingly, for all of the hours I have spent reading the
material on the MS Access DN site, not a single mention that I can recall
of
the pitfalls?

I’d like to think that I only use lookup fields in tables when they are
foreign key references, but just given the quantity of them in my tables I’m
not sure I understand when a foreign key could be a text box, or when it
must
be a combo box with lookup? (I would be grateful if you or someone could
clarify that point for me; what is the rule, logic, or rationale for one
or
the other.)

With regard to your comments and those of others about the use of Value
Lists, Field Lists, List Boxes and the like, I’m much clearer now on their
limitations. While I have used them on occasion, it has only been when I
have
a short list of selections, and only when I know those selections aren’t
likely to change. When I do use them, I incorporate the Limit to List
functions to keep them from growing. But I have found it difficult to
manipulate or process data with them, so I have for the most part left
them
behind opting for an additional table instead and now I may eliminate them
altogether.

To be sure, I have a long, long way to go; but I look forward to reading
more of your writings and the writings of others on this and other topics.

I don’t want to abuse or monopolize anyone’s time here, so I’ll go back to
reading everything I can find on this subject, and post again if I have
any
specific questions.

Thanks again for the post!

Best regards,
Robert


"Allen Browne" wrote:

Okay, lets see if we can clarify things for you here.

In table design, if you choose a field of type "Lookup Wizard", you fire
up
a wizard that many of us don't like. We tend to avoid this wizard because
of
its side effects. You have probably read this:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

But no one is saying that you should not use foreign keys. Every
non-trivial
database has lookup tables -- small tables that contain the valid values
for
a field in a table that looks them up. That is the only way to correctly
normalize your data.

There is a debate about whether the lookup table should have a numeric
field
as its primary key (in which case your foreign key field will also be a
Number), or whether you should use a natural key (Text type.) If you have
a
lookup table for categories, each category name will be unique. If they
are
also relatively brief (just a couple of dozen characters), it makes sense
to
me to use that as the natural key instead of introducing an artificial
key
(such as an AutoNumber.) Sounds like you may have done that in some
cases.

Next the question arises as to whether your main table should use a text
box
or a combo box for the foreign key field. Combos are great on forms, but
many developers avoid them in the table because it masks what data is
really
here. For example, if you use the Lookup Wizard, you get a field of type
Number, but it displays the text. That's confusing for developers and
newbies alike: you actually have to open the table in design view in
order
to build a WHERE clause on the field, because you don't know what
delimiters
you need just by looking at the data.

You also raise the possibility of using value lists rather than a lookup
table. IMHO, that's the worst possible solution. It's trivial to let the
user add more records to a lookup table, but modifying the items in a
lookup
list requires design changes. For more info on this, see:
http://allenbrowne.com/ser-27.html#ValueList

So, FWIW, my recommendations would be:
- Do use foreign keys.
- Avoid the Lookup wizard.
- Use natural keys where appropriate.
- Use combos in your table only rarely.
- A Value List in a combo in a table is too inflexible.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert5833" wrote in message
...
Good day all,

I read recently (having followed some links to various Access related
websites from posts here) that one should avoid altogether the use of
lookup
fields in tables.

Though I am indeed a novice at this, I have created a number of simple
databases over the years, and in my limited understanding thought
lookup
fields were one of the benefits of a relational database? So, I created
lots
of lookup fields in my tables...lots and lots of them.

My question is two-fold. First; what is the risk and what bad things
can
happen to the wayward soul who unwittingly builds lookup fields into
his
tables, and second; if such practice should be avoided like the plague,
how
does one go about eliminating them after the fact?

Maybe one more question; I also use field lists and value lists at the
table
level. Is this the same, and just as bad as having an SQL lookup field
in
a
table?

In a copy of a database I’m working on now, I changed all of the table
level
lookup fields (SQL type) to straight text boxes and I’m waiting now for
that
copy to implode…but it hasn’t yet, and all of my forms are working
correctly.
(I have lots and lots of lookups as the Row Source property.)

Since I changed the table fields to Text Boxes, I haven’t tried to
enter
any
new data…maybe Armageddon awaits me there?

I rarely enter data directly into my tables; and even when I do it's
only
after a first pass at building one, and just before I create a form for
data
entry purposes. Since my forms all have lookup fields in them, will my
house
of cards tumble only if I were to enter data directly into a table?

If anyone has some good insight on this, or would simply to poke fun at
me
for my ignorance or even just offer anecdotal comments on what may be
my
impending doom, I would appreciate any input at all.

Thank you in advance.

Best regards,
RL

"All of the advantages of lookup fields in tables are only there if you
work directly with tables." to that I'd add "or queries"