Need to reference two bound columns in Access 2007 lookup table

[newbieX]

down vote favorite

I have a look-up table (Areas) with 3 columns. Column 1 is the section of the state the county is in, column 2 is the county abbreviation, and column 3 is county name. I have a form (DataEntry) in which I want the user to select the county name, have the county abbreviation value (column 2) get stored in the "County" field and the value in column 1 get stored in the "Section" field. For example, in the Northern area of Utah is Box Elder County (BE). The row of data in the look-up table looks like this: North,BE,Box Elder County. Should the user select Box Elder County, the following values would get written to the "StateLoc" table. County Section BE North The problem is that I can only declare one bound value and I really want to bind two; the abbreviation to write to the County field and section to write its corresponding value to the Section field. Suggestions as to how to approach this?

[pbaldy]

Is this what you're after?

BaldyWeb - Autofill

[Dal Jeanis]

You don't need to have two bound columns to accomplish that. You use one field bound to the boundcolumn (county), and in the afterupdate event of the combo box, you set the value of a text box that's bound to the other field (section).

However, what you are doing is a violation of normalization. Since county uniquely determines both the county abbreviation and the section, you should have the correlation in a second table (which you already apparently do) and then join that table when you need it, rather than storing redundant data in the underlying data table. If county abbreviation is not unique, which it probably isn't, then you should have a primary key, and store that in the data, rather than the county name, section name, and county abbreviation.

[newbieX]

I am not interested in storing the county name in the StateLoc table. I want the abbreviation for future Unique ID number generation if customer application was accepted. (concatenation of county ID + last assigned number + 1) County abbreviation and county name are always unique in the look-up table since we are only looking at counties in Utah. (Section is not though). The user however, does not know the county abbreviation but does know the county name. The look-up table was created solely to limit the choices and standardize the data entered.

You don't need to have two bound columns to accomplish that. You use one field bound to the boundcolumn (county), and in the afterupdate event of the combo box, you set the value of a text box that's bound to the other field (section).

However, what you are doing is a violation of normalization. Since county uniquely determines both the county abbreviation and the section, you should have the correlation in a second table (which you already apparently do) and then join that table when you need it, rather than storing redundant data in the underlying data table. If county abbreviation is not unique, which it probably isn't, then you should have a primary key, and store that in the data, rather than the county name, section name, and county abbreviation.

[pbaldy]

Was post 2 not appropriate?

[Dal Jeanis]

I'm missing something. What's the StateLoc table? Is that where the user's individual records are stored?

Your Areas table already has the the county abbreviation and county name and section, right? And the County Abbreviation is unique, right? So if you store the county abbreviation on the iundividual records, you don't need to store the County Name or Section number on the individual records. You can get that at query time from a join with the Areas table.

[newbieX]

I'm missing something. What's the StateLoc table? Is that where the user's individual records are stored?

Your Areas table already has the the county abbreviation and county name and section, right? And the County Abbreviation is unique, right? So if you store the county abbreviation on the iundividual records, you don't need to store the County Name or Section number on the individual records. You can get that at query time from a join with the Areas table.

That is correct. I guess I do not really need to store the Section value in the StateLoc table.

[Dal Jeanis]

Yeah, that's the wonder of normalization. It takes a bunch of headbanging before it becomes automatic, but after that you save lots of time...