Many man-to-many more questions

[hi there]

just when i think i've got something figured out, something like this comes along and shows me how much i've still got to learn.

i've got another many-to-many problem. here's the tables and some background:

tblEquipment(PK = EquipID)
tblChemical (PK = ChemicalID)
tblReg (PK = RegID)

one piece of equipment can have many chemicals in it.
one chemical can be located in many pieces of equipment.
one reg can apply to many pieces of equipment.

i'd like to set up an entry form where the user first selects the piece of equipment and then populates as many chemicals that are present in that piece of equipment, and then populates all of the regs that apply to that piece of equipment.

right now i have created 3 queries (one for each table) to populate each table (the parents).

i've set up a join table (tblEquipRecords) with the PK's from tblEquipment, tblChemical, and tblReg, but something doesn't make sense. i think this is wrong. how can i have many chemicals associated with one piece of equipment when in the junction table i'm associating each piece of equipment with a particular chemical. do i need multiple join tables?

any help y'all could give this poor lost soul would be extremely appreciated.

thanks everyone.

 

[AncientOne]

Yes, it's easy to start running around in circles with "one-to-many" logic, isn't it?

But, think about it. Each equipment is associated with a particular chemical in your join table. But that equipment can only be combined in the join table with that chemical once. The equipment can appear in the join table often, so can the chemical but their particular combination is unique.

Therefore each list of chemicals for a given piece of equipment is unique in your join table and this enables you to create queries to extract the information you need about particular chemicals or equipments.

If you put regs in your join table as well, you will not have unique rows for (equipment and chemicals) because equipment 1 and chemical1 will have to appear for every reg associated with equipment 1 . Therefore, you need to create a join table between equipment and regs to create unique rows for equpment regs.

You can bring all the information about equipment, chemicals and regs together in queries, once you have set up this structure.

 

[hi there]

hi ancient one,

your explaination helped me out tremendously. i now have 2 junction tables and my form works great.

thanks a lot

 

[raskew]

Need to watch out for that "man-to-many" stuff. This is a family-friendly forum.

"Don't ask, don't tell!"

 

[hi there]

i'll have to watch it. i didn't know my typo had illicit sexual overtones in it.