Many-to-many relations and associated queries

[Dick]

I have a rather complex situation (at least to me) that I can't seem
to solve. I'll try to explain the situation with this simple database
design. The situation is caused by an initial many-to-many
relationship followed by a one-to-many relationship.

Prog Table -- Prog_ID
Prog_Name

The above is the main table.
__________________________________
Link Table -- Link_ID
Prog_ID
BA_ID
LOB_ID
Since each entry in the Prog Table can have many BAs and each BA can
have many Progs this table had to be created.
___________________________________________________
BA Table -- BA_ID
BA_Name

LOB Table -- LOB_ID
LOB_Name

There is a one-to-many relationship between the BA Table and the LOB
Table. Each entry in the BA Table can have many entries in the LOB
Table.
____________________________________________________
My challenge to display only the LOB entries for the selected BA
entries. My forms and subforms display the correct values for the
Prog based on the Link Table but if the user wants to change the LOB
associated with the Prog I want to only allow them to choose a LOB
entry based on its BA entry and no the entire LOB Table.

Thanks in advance.

 

[Jeanette Cunningham]

Dick,
I tried to create the table as you explained below.
I ended up with tables Prog, BA and LOB all related one-to-many to the Link
table.
This is what is shown below. However your description says that LOB and BA
are related one-to-many. I can't see how they are related from your
description.
Which key in BA can have many entries in LOB?


Jeanette Cunningham

 

[Dick]

Dick,
I tried to create the table as you explained below.
I ended up with tables Prog, BA and LOB all related one-to-many to the Link
table.
This is what is shown below. However your description says that LOB and BA
are related one-to-many. I can't see how they are related from your
description.
Which key in BA can have many entries in LOB?

Jeanette Cunningham

I omitted an entry in the LOB Table. It should include BA_ID.

 

[Jeanette Cunningham]

Dick,
I set up the relationships as you described. I found that this query would
show all the LOB's for a particular combination of ProgID and BAID.
Does this help?

SELECT DISTINCT tblLOB.LOBID, tblLOB.LOBName
FROM tblProg INNER JOIN (tblLOB INNER JOIN tblLink ON tblLOB.LOBID =
tblLink.LOBID) ON tblProg.ProgID = tblLink.ProgID
WHERE (((tblProg.ProgID)=2) AND ((tblLOB.BAID)=7))
ORDER BY tblLOB.LOBName;

Jeanette Cunningham

Dick,
I tried to create the table as you explained below.
I ended up with tables Prog, BA and LOB all related one-to-many to the
Link
table.
This is what is shown below. However your description says that LOB and BA
are related one-to-many. I can't see how they are related from your
description.
Which key in BA can have many entries in LOB?

Jeanette Cunningham

I omitted an entry in the LOB Table. It should include BA_ID.