1. Each protocol has many people; Likewise each person can have many protocols
2. Each department has many people and each person can have many department.
<<This is where it gets sticky..
2. Each [person:department] can have many protocols; and each protocol can have many [person:department]'s.
2. The qualifier that discretly identifies each [Protocol:Personepartment] combination is the role. In short, each person:department can be on a single protocol multiple times as long as the role is different.
Example: Protocol 1; John Smith; Department A; Director
Protocol 1 ; John Smith, Department A; Budget Contact
My problem is that functionally the people are entered into the database (frmPeople) and assigned a department (frmPeople.subDepartment) before the protocol is entered. I would like to have frmProtocol.subInvolved to be able to build this unique f-way relation.
Ideally the user will have a continuous form frmPeople.subInvolved with 3 combo boxes, cboPeople, cboRole, cboDepartment. The user will select a person from cboPeople, the the appropriate role from cboRole, and then the cboDepartment will be filtered to allow only those departments which are valid for the person in cboPerson.
To accomplish this I created a table tblDeptPerson which holds the people:department combinations, however since each person can have more than one entry when the user selects the cboPerson those people with more than one department have an entry for each department.
QUESTION: How do I limit the cboDepartment list to only those department which correspond to the person selected in cboPerson AND have each person listed ONLY 1 time in the cboPerson control?
This is a great forum and I have learned a tremendous amount from you all. If you have any suggestions please help. Thanks in advance!