Get same info from table based on different id\criteria

[BRZ-Ryan]

I have a query where I am trying to list 2 employees names each for a different criteria\reason (each query result should list 2 employee names). One of the ID numbers is for an Attendee of an event (working fine), the other ID is the scheduler of that event (not working). I'm not sure how to go about adding the scheduler.

The scheduler ID is stored in Events.ScheduledBy and the employee ID is Security.ID and name Security.LastName. I can do this with a dlookup but I'd prefer to not use it.

Original query (without the ScheduledBy part.

SELECT Security.ID, Events.ID, Security.FirstName, Security.LastName, Events.EventDate
FROM Security, Events, Attendance
WHERE ((Events.EventDate = txtSearch.Value) AND (Events.ID = Attendance.EventID) AND (Attendance.EmployeeID = Security.ID));

I tried this, but then it only lists events where the attendee and scheduler is the same person. If I use just Events.ScheduledBy in my SELECT statement, it properly pulls the ID of the scheduledby person, but I want it to pull the name based on that ID. But I need the query to differentiate between the attendee and scheduledby person.

SELECT Security.ID, Events.ID, Security.FirstName, Security.LastName, Events.EventDate, Security.LastName
FROM Security, Events, Attendance
WHERE ((Events.EventDate = txtSearch.Value) AND (Events.ID = Attendance.EventID) AND (Attendance.EmployeeID = Security.ID) And (Events.ScheduledBy = Security.ID));

This provides the result I want, but with DLookUp (if it helps you understand my issue)

SELECT Security.ID, Events.ID, Security.FirstName, Security.LastName, Events.EventDate, DLookUp("[LastName]","Security","[ID]=" & [Events].[ScheduledBy]) As Scheduler
FROM Security, Events, Attendance
WHERE ((Events.EventDate = txtSearch.Value) AND (Events.ID = Attendance.EventID) AND (Attendance.EmployeeID = Security.ID));

[June7]

The attendee and scheduler name info are both in the same table (Security)?

Suggest using join clauses in the query.

You can include Security table in the query twice. Join one to Attendance and one to Events.

Join Events and Attendance.

[BRZ-Ryan]

The attendee and scheduler name info are both in the same table (Security)?.

Yes

Suggest using join clauses in the query.

You can include Security table in the query twice. Join one to Attendance and one to Events.

Join Events and Attendance.

Can you give an example? I've came a long way since I started but never did a join twice in a query.

[June7]

Use the query builder. Drag the Security table into the design window twice. Click field and drag to other table to create join link between the PK and FK fields.

[BRZ-Ryan]

You're a genius. Thanks again