Search on one field & fill in the correct matching record
in [Tables]
|
Prev: How can I set one field to return data into another field?
Next: Challenge: One table associated with several fields - but avoidin
From: Ron on 5 Dec 2009 12:12 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?
From: Ken Snell on 5 Dec 2009 12:30 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" <Ron(a)discussions.microsoft.com> wrote in message news:6184FA37-421B-4B66-8C06-0A2813ED6806(a)microsoft.com... > 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?
From: Lynn Trapp on 5 Dec 2009 12:33
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" <Ron(a)discussions.microsoft.com> wrote in message news:6184FA37-421B-4B66-8C06-0A2813ED6806(a)microsoft.com... > 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? |