If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Search on one field & fill in the correct matching record
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? |
#3
|
|||
|
|||
Search on one field & fill in the correct matching record
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? |
Thread Tools | |
Display Modes | |
|
|