Linking Junction Table to Unrelated Table

[dashingirish]

Still very much a newbie at Access, so apologies up front for the raw simplicity of this question.

I've got a main table TblMain and a junction table TblJunction with a one-to-one relationship.

I'm using TblMain as the form and TblJunction as a subform.

Clearly, I'm missing the bit where a record in TblMain is linked with a record in TblJunction because I can't even run a query on both tables. It seems like it would be easy, but I'm just not seeing the solution.

Thanks for any help you can offer.

 

[tehNellie]

it would help if you elaborated on what it is that you're trying to achieve. What you've described isn't a junction table.

Without knowing what's in tblMain or tblJunction or what you want to do with that information it's going to be difficult to give you advice, but what you're describing so far isn't a true junction table which is normally used to facilitate a Many-to-Many relationship.

 

[dashingirish]

I'm happy to elaborate. Thank you for your quick response.

The db is to collect client assessments made up of many items, such as Housing, Education, etc. Each item is defined by a priority (Primary Concern, Not a Concern, etc) and a Status (In Crisis, Thriving, etc). For every item, the value choices for priority and status are identical. However the definition for the status values is different for each item. For example "In Crisis" for item Education is defined as "Basic skills at or below grade school level." For Housing, "In Crisis" is defined as "Homeless."

I figured I'd need a generic tblPriority and, for each item, a tblItemStatus, containing the status along with the definitions, with a junction table linking the two tables. My db would end up with a single tblPriority and numerous tblItemStatus and junction tables linking the two tables.

What I'm trying to figure out is how to link the tblPriority and tbleItemStatus to the main assessment table so that for each unique assessment a unique row in each of those tables is linked.

I approached it this way because I was trying to keep the main table from becoming unwieldy, but I'm now that I'm writing all that out, I'm thinking that this is kind of nuts.

 

[rodmc]

Im not sure that you need to create a junction table at all. As tehNellie said a junction tabel is only required if you need to break a many to many relationship into 2 one to many's

Why do you need a separate table for priorities? Can you not just create a combo based on a query?

I think your making this a too difficult than it needs to be