This db is for scheduling employees to go to events. I'm trying to make it easier for the schedulers to only populate the people combobox with people who are not already scheduled for the date they are currently scheduling (the combo is populated after a date is chosed on the form, which is Forms!Navigation!NavigationSubform.Form!EventDate) . Forms!Navigation!NavigationSubform.Form!txtID is the current event being entered.
-When an event is created, the Events table holds the ID of the event and the event date
-When a person is assigned to an event, a record is created in attendance table with the Event ID and Security ID
My logic is:
Code:
Where not exists
-Event for same as selected date
-Event ID <> current event ID
-EventID = Attendance.EventID AND Security.ID = Attendance.EmployeeID
Tables:
Code:
Events
-ID
-EventDate
Security
-ID
-FirstName
-LastName
Attendance
-EventID (=Events.ID)
-EmployeeID (=Security.ID)
What I added to the existing query
Code:
AND Exists (
SELECT Security.ID
FROM Security, Events, Attendance
WHERE ((Events.EventDate = Forms!Navigation!NavigationSubform.Form!EventDate) AND
(Events.ID <> Forms!Navigation!NavigationSubform.Form!txtID)) AND
(Events.ID=Attendance.EventID AND
Attendance.EmployeeID = Security.ID))=False))