I am trying to perform a query to grab all people with a particular role, AND also grab other people IF they are found in the Attendee field for the currently selected Event (role does not matter for the second part).
Code:
Table=Events
Fields:
ID
Attendees (From People table: Name1,Name2,Name3) (Yes, I know I should normalize)
Table=People
Fields:
ID
Name
Role
Scenario to populate listbox:
Select all people from People table with role="Attendee"
Select people from People table IF they exist in the Attendee field (can use InStr()) for the current event ID.
The first part is simple, but I'm not sure how to perform the second query. When I use an temp attendee, they are added to the People table with the role of "Attendee (temp)". We do not want these people to appear in the Attendee listbox for events that they are not a part of OR when creating new event. They should only be included in the listbox in the event(s) for which they were manually added and are found in the Attendee field for the event (I have a command button to add these temps). We re-visit records up for 4 times before they are completed and the temps name(s) re-appearing in the listbox is important.
Event1
Ryan Attendee
Bill Attendee
Bob Attendee (temp)
If I create a new event, I should see only "Attendees" from people table
If I re-visit Event1, I should see all "Attendees" from people table AND "Bob" since he is in the attendee field for Event1.
I made this for grabbing the non Attendees, which is probably wrong. Please help (Again, the role does not matter for the temps, I had to include that in my code to prevent every Attendee from being returned). I wrote this in VBA to allow the ID to be dynamic based on the currently viewed event, me.id being the Event ID.
Code:
strSQL = "SELECT DISTINCT People.Name" & vbCrLf
strSQL = strSQL & "FROM Events,People" & vbCrLf
strSQL = strSQL & "WHERE ((People.Role= 'Attendee (temp)') AND (" & Me.ID & " = DLookup('[ID]', '[Events]', InStr([Attendees],[People].[Name])>0)))" & vbCrLf
lstAttendees.RowSource = strSQL
lstAttendees.Requery
I need that, combined with
Code:
SELECT People.Name
FROM People
WHERE People.Role <> "dbadmin"
ORDER BY People.Name;