Junction Tables and Forms

[dashingirish]

Really dumb with Access, but I'm hoping you can steer me in the right direction. I'm hopeful I can ask this question correctly, so that it is easier to answer.

Quickly, I'm building a db for client assessments. Each assessment is made up of a series of items, for each the user must select importance (i.e. Not Important, Very Important) and a status (In Crisis, Stable). Although the choices for importance and status remain static for every item, the definitions for status change for each item. For example, for the item HOUSING, In Crisis is defined as "Homeless." For the item EDUCATION, In Crisis is defined as "Illiterate." Those aren't the actual definition, but you get the idea. I ended up created a junction table for each item linking a table for that item's importance and a table for that item's status.

I'd like to build a form which serves as the assessment, restricting users to enter only one importance/status per item. For example, one item would be labeled "Housing" and the user would select an importance and a status from two drop-down boxes. Beneath Housing would be the item, say, Education with two drop-down boxes.

My question is: do I use the fields from the two tables or do I use the junction table as these drop-downs? Is there some trick I'm just not getting? This is probably really simple, but I'm just baffled - probably over-thinking.

Thank you in advance for your patience and help.

 

[boblarson]

You use the junction table as the form's record source but the combos on the form get their ROW SOURCE from the respective tables but the combos are BOUND to the fields in the junction table.

 

[dashingirish]

Thank you for the quick reply.

Perhaps you wouldn't mind loaning me another $.02 -

Do you think this is the best way to approach the data? I'm going to end up with so many status tables and junction tables in my db. I'm worried they may get out of hand down the road.

Alternately I can include the importance for each item as a field in the main assessment table with status linking out to table containing each item's status choices. I was trying to keep the size of the main table smaller, but I'm thinking that this might be easier to maintain.

tblMain
ID
HousingImportance
HousingStatusID
EducationImportance
EducationStatusID

tblHousingStatus
ID
Status
StatusDefinition
StatusNumericValue

tblEducationStatus
ID
Status
StatusDefinition
StatusNumericValue