Which is best: Lookup wizard or Manual Lookup

[smitj2]

Recently I had a discussion with a colleague about creating lookup
tables in Access. She routinely used the lookup wizard to make it
possible for a field to contain the values from another table. I set
it up manually using the lookup tab on the table properties.

We then noticed that her way created a relationship that was visible
in the relationship window whereas my way didn't.

Why the difference and what are the relative merits. Indeed should
lookup be done from a form rather than a table.

We are both new and eager to learn.

 

[Allen Browne]

The biggest problem with using a combo to display a field in a table (rather
than just a form) is that people get confused about what is stored there.

A common scenario is to use a Number field, but make the number column
zero-width so the text part displays, even though it is a Number field. Now
someone goes to build some criteria, and can't figure out why it doesn't
match the text, or the quote marks are not needed around the target value.

To add to the confusion, the lookup wizard does other things such as
creating the relation and indexes which may duplicate what's already there.
For more info on why some developers eschew the lookup wizard completely,
see:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

Note that these comments to NOT apply to:
a) using lookup tables: these are essential, even if you hate what the
lookup wiz does;
b) using combos in forms.