JOIN problem..

[CrystalSurfer]

I have 3 tables:
tblClient (our Company customers)
tblContact (people who work at the Companies)
tblClientRegulation (1 or more types of Regulation that the Company is obliged, by law, to comply with)

Relationships:
tblClient - tblContact: 1 to many
tblClient - tblClientRegulation: 1 to many

For most companies there is just one Contact who deals with the Company's Regulation. We set them as a 'Primary Contact' (tblContact.PrimaryContact = true) and there is only 1 Primary Contact per Client.

However for some very large Companies there may be 1 contact that deals with one Regulation and another Contact that deals with another Regulation.
I added Regulation.ID (from the master table tblRegulation) as a Foreign Key to Contact to enable a Contact to be specific to Regulation. This means we are able to have either:
a: 1 Primary Contact with Null as Regulation (they manage all Regs)
OR
b: Multiple Primary Contacts with specific Regulations (and no Contact with a null Regulation)

Problem
I wish to be able to retrieve Contact details specific to a particular Regulation. But how can I create a join query that retrieves both the Client and Contact data when the Contact.Regulation could be either Null or a specific Regulation?

Hope I've been clear enough - let me know if not!
Thanks..

 

[neileg]

If contacts are responsible for regulations, I would join them that way.

tblClient - tblContact: 1 to many
tblContact - tblRegulation: many to many

I don't like using null to mean something positive, so I would add a record to tblRegulation that indicates primary contact. Then if you want to know who deals with regulation XYZ you can test for 'XYZ' OR 'Primary Contact'.

 

[CrystalSurfer]

Neil, you're a genius. Thank you.
Why didn't I think of that? - putting Contact on tblClientRegulation..
Seems so obvious now - lol