Populate 2nd Form Control and Table

[KMEscherich]

Using Access '97

Hi there, am wondering if someone can please help me with the following:

I have a master table (T_INVESTIGATIO N) that contains the following fields as well as other fields.

T_INVESTIGATION
LOC_CODE,
STR_ADDR,
CITY,
STATE,
ZIP_CODE
COST_CTR,
SITE_LOC

I also have some LOOKUP tables that are linked to T_INVESTIGATION fields
LT_LOC_CODE
LT_STR_ADDR
LT_CITY,
LT_STATE,
LT_ZIP_CODE
LT_COST_CTR,
LT_SITE_LOC


I have a lookup table associated with each of the 7 field listed above. I have been asked for the following:

1) Set up the form so that when someone clicks on the LOC_CODE control on the form that the STR_ADDR, CITY, STATE and ZIP_CODE all populate automatically so the end-user doesn't have to make an entry for the STR_ADDR, CITY, STATE and ZIP_CODE. After making the selection, by all means, all controls MUST populate the information into the T_INVESTIGATION table fields accordingly for each record.

2) Set up the form so that when someone clicks on the COST_CTR drop-down control (which is tied to the LT_COST_CTR lookup table) on the form that the SITE_LOC control populates with a listing according to the selection the end-user makes. Both of these need to be populated into the T_INVESTIGATION table fields as well for each record.

In other words, if I select COST_CTR 100 from the drop-down list, I will only see SITE_LOC ( 12, 13, 14, 15) and therefore, I make my selections of COST_CTR = 100 and SITE_CTR = 12 and these selections get populated in the T_INVESTIGATION table in the two different fields. If I select COST_CTR 200, I will only see SITE_LOC (22, 23, 24, 25) and therefore, I make my selections of COST_CTR = 200 and SITE_CTR = 24, and of course these get populated as well into the T_INVESTIGATION table in the two different fields.

I hope this is described clearly enough for someone to help me.

Thank you VERY MUCH

[MMcCarthy]

The first thing you need to do is add STR_ADDR, CITY, STATE and ZIP_CODE to the LT_LOC_CODE table. If they are related and dependent on LOC_CODE they should be in the same table.

Once you have done this then the values shouldn't be also stored in the T_INVESTIGATION table as this is duplication of information. You will need to follow similar rules for the COST_CTR.

Have a look at this tutorial on Table Structures.

Database Normalization and Table Structures