Search on one field & fill in the correct matching record

Using Access 2003 I created a table called tblcustomer. The table has seven
fields: *CustomerID, Fname, Lname, Address, City, State, & Zip. When entering
a customer record I would like to be able to select the zip code in a drop
down list box. Once the zip code is selected I would like to be able to save
the correct matching city, state & zip for that record in the tblcustomer.
Since all my customers are local there is a lot of redundentcy on the city,
state & zip fields. I thought it would be best to have seperate tables for
these fields - tblcity, tblstate, tblzip. I now need a table to match up the
correct zipcode to city & state. The table is called tblctystzp. The fields
in this table are *ctystzpID, city, state & zip. Each one of these fields can
select the correct info from a drop down box which is reference to tblcity,
tblstate & tblzip. Should I NOT have the city, state & zip fields in the
tblcustomer and only a reference from each record in the tblcustomer to
tblcitystzp. The relationship between the tblcustomer & tblctystzp tables
will be a many to many, so I now need a junction table. Am I going about this
the correct way?

Don't store city, state, etc. in your tblCustomer table. Use the zipcode
field to link to those data. You don't need to store redundant data (city,
state) in two tables.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"Ron" wrote in message
...
Using Access 2003 I created a table called tblcustomer. The table has
seven
fields: *CustomerID, Fname, Lname, Address, City, State, & Zip. When
entering
a customer record I would like to be able to select the zip code in a drop
down list box. Once the zip code is selected I would like to be able to
save
the correct matching city, state & zip for that record in the tblcustomer.
Since all my customers are local there is a lot of redundentcy on the
city,
state & zip fields. I thought it would be best to have seperate tables for
these fields - tblcity, tblstate, tblzip. I now need a table to match up
the
correct zipcode to city & state. The table is called tblctystzp. The
fields
in this table are *ctystzpID, city, state & zip. Each one of these fields
can
select the correct info from a drop down box which is reference to
tblcity,
tblstate & tblzip. Should I NOT have the city, state & zip fields in the
tblcustomer and only a reference from each record in the tblcustomer to
tblcitystzp. The relationship between the tblcustomer & tblctystzp tables
will be a many to many, so I now need a junction table. Am I going about
this
the correct way?

Ron,
There are no triggers in Access tables, so there is nothing to cause those
updates to automatically happen. However, if you have a separate table to
store the city and state information, then it can be related to the customer
table as you have suggested. all you will need to store in the customer
table is the Zip Code field, then you can use the combobox as you suggested
and query the 2 tables to get the full information.

--
Lynn Trapp
Trainer/Application Developer


"Ron" wrote in message
...
Using Access 2003 I created a table called tblcustomer. The table has
seven
fields: *CustomerID, Fname, Lname, Address, City, State, & Zip. When
entering
a customer record I would like to be able to select the zip code in a drop
down list box. Once the zip code is selected I would like to be able to
save
the correct matching city, state & zip for that record in the tblcustomer.
Since all my customers are local there is a lot of redundentcy on the
city,
state & zip fields. I thought it would be best to have seperate tables for
these fields - tblcity, tblstate, tblzip. I now need a table to match up
the
correct zipcode to city & state. The table is called tblctystzp. The
fields
in this table are *ctystzpID, city, state & zip. Each one of these fields
can
select the correct info from a drop down box which is reference to
tblcity,
tblstate & tblzip. Should I NOT have the city, state & zip fields in the
tblcustomer and only a reference from each record in the tblcustomer to
tblcitystzp. The relationship between the tblcustomer & tblctystzp tables
will be a many to many, so I now need a junction table. Am I going about
this
the correct way?