why are look-up fields cautioned against?

[Guest]

I've seen it recommended in various postings and database websites that you
should avoid having look-up fields in your tables. I'd like to understand
this better. Why do experts advise against them, and how can you create
relationships between tables without using a look-up field?

 

[Fred Boer]

Dear dbnDavid:

Before going on, please be aware that the problem with "look up fields" is
their use in table datasheets - *not* with looking up data in separate
tables...

The following link explains the problems:

http://www.mvps.org/access/lookupfields.htm

Here's a quote from a post by expert John Vinson:

That's because the Lookup field type is very limited, misleading,
misdesigned, and all but useless. I'd recommend that it NEVER be used,
period.

To get values from another table, there are two techniques that are
better (IMHO) than the Lookup tripe. One would be to use a Query
linking the two tables - if you're creating a Report, this is a good
choice.


The other, better for data entry and browsing, is to create a Form
based on your table and use combo boxes on the Form. These should be
based on your "lookup" tables, using the numeric ID as the bound
column but the meaningful name as the first nonzero width value -
you'll see the name, the computer will see the ID, and you'll both be
happy.


To get one combo dependent on another combo (on a Form, it won't work
on a table datasheet), you need to base the second combo on a query
which uses the first combo as a criterion. Create and save a Query as
the RowSource for Combo2, using


=Forms![NameOfYourForm]![Combo1]


as a criterion.


Then, in the AfterUpdate event of Combo1, click the ... icon, select
Code Builder, and put one line of code between the Sub and End Sub
lines that Access will give you:


Private Sub Combo1_AfterUpdate()
Me!Combo2.Requery
End Sub


(changing the combo names to those in your form, of course).


John W. Vinson[MVP]

 

[Guest]

Fred did a great job of answering your question except for the question
regarding creating relationships. For that, you use the relationship
builder. (Tools, Relationships). Before doing that; however, you need to
address your table structures so each table has a Primary Key and an Child
tables have, in addition to their own Primary Key, a Foreign Key that is used
in the relationship.
For example, you have an order table and an order detail table. The order
table contains information about the order and the order detail table
contains 1 or more records for each order that describes information about
what was ordered:

tblOrder
ORDER_ID (AutoNumber - Primary Key)
ORDER_NUMBER
ORDER_DATE
CUSTOMER_ID (Long Integer - Foreign Key to customer table to get customer
info)

tblOrderDetail
ODR_DTL_ID (AutoNumber - Primary Key)
ORDER_ID (Long Interger - Foreign Key to Order table to associate this line
to the
correct order)
PRODUCT_ID (Long Integer - Foreign Key to Locate product ordered)
ODR_QTY
ODR_PRICE

Then in the Relationship builder you associate the ORDER_ID of the Order
table with ORDER_ID in the Order Detail table. You also associate the
CUSTOMER_ID of the customer table to the CUSTOMER_ID of the order table and
the PRODUCT_ID of the Inventory table to the PRODUCT_ID of the order detail
table.

 

[Guest]

Thank very much Fred and Klatuu. I'm continuing to digest the info, but it
has certainly clarified things for me. The short message I get is that lookup
fields do have to be present in a table as a necessary part of table
relationships, but that's all you ask them to do!

 

[Guest]

Sorry, that is not what they are for. Here is a quote from Access Help that
may clarify their use for you:

A Lookup field provides a list of values that you can choose from when you
are entering data. This makes data entry easier and ensures the consistency
of the data in that field. A Lookup field can get its list of values from a
table or query, or from a fixed set of values that you have specified.

 

[Douglas J Steele]

The short message I get is that lookup
fields do have to be present in a table as a necessary part of table
relationships, but that's all you ask them to do!

Not at all. I always have relationships in my databases, and I've never had
a lookup field in any of them.

 

[Fred Boer]

Dear David:

At the risk of making it look like you are being mobbed...

No. Lookup fields are not necessary. Looking up data in related tables *is*,
but *not* using lookup fields. When I started with Access it took several
tries before one of the experts here actually got through to me on this...

Maybe an example might help? (Apologies if this seems simplistic...)

Suppose you have a database listing customers. They live all over the world.
Every customer address includes a city. .

tblCustomer

CustomerID
Name
City

Aha! You realize that you should have a separate table for city names! So
you create a table for cities, maybe something like this:

tblCities

CityID
CityName

So, you want to change the customer database, right?

tblCustomer

CustomerID
Name
Ci...

Hold it... you think.. shouldn't that be a lookup field? I mean, I want to
"look up" that information in the "cities" table, right?
At this point, you need to stop. Don't create a lookup field in the
"customer" table. Create a field called "CityID". Make it the "number"
datatype.
Now, close and save the new table design. Go to Tools>Relationships (or
click on the button). Add both tables to the design form. Click on the
"CityID" field in tblCustomer and drag it on top of the "CityID" field in
tblCities. A relationship window opens up. Check the enforce referential
integrity checkbox, the click on "Create". You have created the necessary
relationship.

Now... what next? Well, you *don't* use the datasheet to do data entry. The
next step is to create a form. (Use the wizard...). Then open this form in
design view. Click on the combobox button in the toolbox. Drag a combobox on
the form. The combobox wizard will run. Choose the option to look up the
values in another table. Follow the wizard.

Congrats! You have a combobox which presents a list of cities to choose
from. The combobox is bound to the CityID field in tblCustomer.

So you can "look up" the data in a related table, but you don't have a
"lookup field" in the Customer table...

HTH
Fred

P.S. BTW, thanks Klaatu for picking up my fumble earlier in the thread.
P.P.S. Of couse tblCities doesn't necessarily need a CityID field (Primary
key could be CityName)...
P.P.P.S. And, of course, one could also use a form/subform..
P.P.P.P.S. We'll be here when you try to figure out exactly how the combobox
works!

 

[Fred Boer]

P.S. BTW, thanks Klaatu for picking up my fumble earlier in the thread.

Dang it...sorry, that should have been "Klatuu"...

Ferd

 

[John Vinson]

P.P.S. Of couse tblCities doesn't necessarily need a CityID field (Primary
key could be CityName)...

ummm...

Las Vegas is a big and strange city in Nevada.
Las Vegas is a medium-sized pleasant city in New Mexico.
Las Vegas is a small town in California.
Las Vegas is...

<g>

Names don't usually make good primary keys!

John W. Vinson[MVP]

 

[Fred Boer]

Dear John:

Sigh. Thanks for keeping me on the straight and narrow.

Could I explore this a bit more, though? What's the best way to cope with
this? I have a city table which I use to list the place of publication for
books. I also have a Province/State table listing, well, province and state
names (used for Patron address information, actually)... Until now, I've
just ignored the fact that different places might share the same name, but
your correction has made me think. Should I do something like this if I
wanted to be more accurate? (And I suppose that country name data would
possibly be involved, but let's limit it to provinces and U.S. States...)


tblCity

CityID
CityName
ProvinceOrStateID

tblProvinceOrState

ProvinceOrStateID
ProvinceOrStateName

Thanks!
Fred

 

[John Vinson]

Dear John:

Sigh. Thanks for keeping me on the straight and narrow.

Could I explore this a bit more, though? What's the best way to cope with
this? I have a city table which I use to list the place of publication for
books. I also have a Province/State table listing, well, province and state
names (used for Patron address information, actually)... Until now, I've
just ignored the fact that different places might share the same name, but
your correction has made me think. Should I do something like this if I
wanted to be more accurate? (And I suppose that country name data would
possibly be involved, but let's limit it to provinces and U.S. States...)


tblCity

CityID
CityName
ProvinceOrStateID

tblProvinceOrState

ProvinceOrStateID
ProvinceOrStateName

There are actually two cities named Los Alamos in New Mexico (one's
off on a dirt road twenty miles from Las Vegas, oddly enough) so you
can't even count on a two-field city/state index. I don't know if
there IS a really good way, though the US Postal Service's ZIP code
database (with its Preferred and Alternate City Names) may be the best
bet...

John W. Vinson[MVP]

 

[BruceM]

Clearly there is potential murkiness with storing CityID by itself. If one
Los Alamos changes its name there is no way of editing the record in tblCity
without every Los Alamos assuming the new name. The combo box from which
City is selected could be a two-column combo box that includes the Zip code,
but what happens is the municipality grows to the point where it has two or
more Zip codes, or if a larger city has two or more Zip codes to start with,
or if there is Zip code from, say, Las Vegas that has not yet been used?
Since City is a single field I would be inclined to store the name rather
than the ID, although a database that includes only local addresses may be
handled differently from one that includes a nationwide listing in which
some towns may appear only once. So many decisions...

 

[Fred Boer]

Thanks to you both!

Fred

Clearly there is potential murkiness with storing CityID by itself. If
one Los Alamos changes its name there is no way of editing the record in
tblCity without every Los Alamos assuming the new name. The combo box
from which City is selected could be a two-column combo box that includes
the Zip code, but what happens is the municipality grows to the point
where it has two or more Zip codes, or if a larger city has two or more
Zip codes to start with, or if there is Zip code from, say, Las Vegas that
has not yet been used? Since City is a single field I would be inclined to
store the name rather than the ID, although a database that includes only
local addresses may be handled differently from one that includes a
nationwide listing in which some towns may appear only once. So many
decisions...

 

[John Vinson]

Clearly there is potential murkiness with storing CityID by itself. If one
Los Alamos changes its name there is no way of editing the record in tblCity
without every Los Alamos assuming the new name. The combo box from which
City is selected could be a two-column combo box that includes the Zip code,
but what happens is the municipality grows to the point where it has two or
more Zip codes, or if a larger city has two or more Zip codes to start with,
or if there is Zip code from, say, Las Vegas that has not yet been used?
Since City is a single field I would be inclined to store the name rather
than the ID, although a database that includes only local addresses may be
handled differently from one that includes a nationwide listing in which
some towns may appear only once. So many decisions...

What I've usually done is use a Zip table with fields for Zip, City,
and (redundantly though it helps for filtering) State. I've got a
generic Address subform which filters the City combo box on updating
the Zip, and vice versa; if selecting a zip uniquely identifies a city
(typical) it just fills it in, and if selecting a city uniquely
identifies a zip (small towns), it just fills THAT in.

John W. Vinson[MVP]

 

[Guest]

Dear Fred

It's been some time since your posting that I'm replying to here, but I just
needed to tell you that your advice really gave me a big push upwards on my
Access learning curve. I'm glad I asked the question and I'm continuing to
absorb and digest what I what you've taught me here. Thanks very much!

 

[Fred Boer]

Happy to been of help, David! Good luck in your project!

All the best!
Fred