Lookup tables

[G deady via AccessMonster.com]

Can someone explain the value to me of lookup tables. I have a db for my law
firm. I created several lookup tables -1 for all the names of the Judges
where we practice, 1 for all the police depts, 1 for all the attorneys in our
county and the biggie- all the zip codes in the country listed by city and
state. But having done this I am now not sure of its value. Take the
zipcodes table. I have a data entry form to enter daya for new clients. I
have fields for client name and address and a combobox that looks up city
state and zip and displays in text boxes on form. Since the values in these
text boxes are not saved to my client table what are their value? If I later
want to print a report or mailing label for that client I will not have his
city state and zip saved. I understand the concept of normalization and the
need to avoid repetitive data but I don't understand how these lookup tables
help me. Can someone explain?

 

[Duane Hookom]

Apparently lookup tables can be very helpful. They keep data (not "daya")
spelled consistently and speed up data entry. You can add your lookup tables
to report or form record source queries to display the related values.

Don't confuse lookup fields with lookup tables. You can read about the evils
of lookup fields at http://www.mvps.org/access/lookupfields.htm

 

[G deady via AccessMonster.com]

Duane, I have a general understanding of what you are talking about but here
is my question: I have a table:
tbl_client_information
----------------------
client number(autonumber-pk)
fname
mi
lname
dtebrth
socialsecurnumb
StreetAddress
Hphone
Wphone
fax
cell
email


I then have a lookup table populated with every zip code in the US
tbl_zipcode
-----------
zipcodeid(autonumber pk)
city
state
zip

I populate my client_info table with a form that has all the table fields
plus 3 text boxes to hold the values of the city, state and zip for that
client which the user has looked up in a combobox on the form linked to the
zipcodes table. My question is if I later want to generate a report of the
names and addresses of my clients aren't I going to have a problem since the
the city, state and zip for each client havent been saved to the clientinfo
table? The have obly been looked up and displayed temporarily as values in
text boxes on the data entry form. Obviously I am new and not understanding
something. Please help.

 

[Duane Hookom]

You should add a zipcode field to your client information table. You can
then join to the zipcode table when you need to display the related city and
state.

 

[Sharkbyte]

I'm wondering why you are looking up the City/State/Zip if you are not saving
a reference to it, along with the client record.

As Duane states, add a zipcodeid field to your Client table. Then save the
id number, when you create your customer record. Now you always have their
City/State/Zip.

Sharkbyte