When the user selects a patient in one combobox, the other one should only display a unique list of volunteers who are matched with that patient in the Service Events table. Conversely, when the user selects a volunteer, the patient combobox should only show patients matched with that volunteer in Service Events. (Service Events is the record of all patient/volunteer contacts and it is the object of the filter whose parameters are selected with these combos.)
The objective is, for filtering purposes, if the user selects either a patient or a volunteer and leaves the other combo blank, they’ll see all the visits that person received/provided. But if they want to see the visits between a particular patient and volunteer, after making a selection in one combo, the other one will only show the relevant options.
I made a test query using three tables: tblPatients; tblVolunteers; and tblSvcEvents. I set the Unique Values to true and manually plugged in a PID (patient ID*). That produced exactly the volunteer results I want with the SQL statement:
Code: Select all
SELECT DISTINCT tblPatients.PatientID, tblPatients.PID, tblPatients.FirstName,
tblPatients.LastName, tblVolunteers.VolunteerID, tblVolunteers.VID,
tblVolunteers.FirstName, tblVolunteers.LastName, tblSvcEvents.PID, tblSvcEvents.VID
FROM tblVolunteers INNER JOIN (tblPatients INNER JOIN tblSvcEvents
ON tblPatients.PatientID = tblSvcEvents.PatientID)
ON tblVolunteers.VolunteerID = tblSvcEvents.VolunteerID
WHERE (((tblSvcEvents.PID)="GT01"));
*[Regarding PatientID and VolunteerID in the SQL statement, those are the auto-number record ids. PID and VID are unique identifications assigned to each person.]