Lookup field abilities

R

Rich

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?
 
R

Rich

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?
 
A

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

Rich

Allen, thanks again for the response. With the text-based lookup field like
you suggested, how much more disk space does it require using this method?
The increase in size won't be comparable to having the same data in a flat
database, will it? I know the data isn't flat but having to repeat a
not-so-basic ID field seems like it might be troublesome for file size as
well.
 
J

John Vinson

Allen, thanks again for the response. With the text-based lookup field like
you suggested, how much more disk space does it require using this method?
The increase in size won't be comparable to having the same data in a flat
database, will it? I know the data isn't flat but having to repeat a
not-so-basic ID field seems like it might be troublesome for file size as
well.
Click to expand...

If you're pushing two billion bytes... you've got bigger worries than
a lookup field!

If you're not over a billion bytes... don't worry about it. Disk is
cheap, and JET is pretty efficient.

John W. Vinson[MVP]
 
Top