Lookup field abilities

[Guest]

Tell me if I have this straight.

Setting up a Lookup Column in a field to view the real value versus a record
ID is really best suited for the developer because of all the confusion with
extra relationships, right?

When performing any type of query, you still have to set-up the
relationships like before; you cannot simply perform a query using the
Look(ed)-up values in a single table, correct?

 

[Allen Browne]

A column created by the Lookup Wizard in table design is best suited for a
marketing department to pretend to make Access easier, while actually making
it more confusing for everyone.

See:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

 

[Guest]

Allen, after reading your link, I was wondering, is there a way to build a
functionally equivalent set-up without all the problems? I really like the
ability to expand and collapse data from the datasheet.

My current project will be a reference database that won't ever be updated
once I configure everything properly. Because of this, is there any way to
"extinguish" the problems with this feature since I won't ever need to change
any data?

 

[Allen Browne]

A simple solution is to use a text-based lookup field.

For example, in the Northwind sample database that installs with Access,
there is a table named Categories, with a CategoryID field (number) and a
Category Name field (text). Instead, you could create the Categories table
with a CateogryID field that is *Text*, and put the category names in there.
Then in your Products table, you have a matching Text field for CategoryID.
You still create the relation between the 2 tables, and this prevents
incorrect spelling of categories, but the data is visually meaningful
without hiding columns and all the other problems that the lookup wizard
creates.

As a side-benefit, this solves the problems with lookup values that
disappear from combos when you filter their RowSource in a continuous
form--something that people are asking about every few weeks in these
forums.

This is something I do by default now for small lookup tables. 24-characters
is usually enough, unless it's for a government department: they seem to
want whole sentences for their category names.

BTW, just to be 100% clear, the problem is not with having lookup tables
containing a list of acceptable values for a drop-down list: that's an
essential concept. The problems are with what the lookup wizard does in
hiding what is actually stored, creating unnecessary objects behind the
scenes, and generally confusing everyone in a way that is completely
unnecessary.