Does this query look correct

[BRZ-Ryan]

My below query works perfect, but if I remove the GROUP BY clause, it returns many duplicates of each name. This makes me think my query is wrong, and possibly not efficient. Can someone take a look to confirm it looks correct. If the group by clause is not expensive to performance then it may be a waste to investigate but I wanted to get an opinion.

The query populates a combobox with names when an Event is selected. I want all names in the first condition to always appear, and the second condition to use names only if there is an attendance record for the person for the current event in the Attendance table.

Tables:

[Events]
ID

[Attendance]
ID
EventID
EmployeeID

[Security]
ID
FirstName
LastName
Access

Conditions:
- If their Access is: 'Attendee', 'Scheduler', 'Admin' (this one is simple)
OR
- If their Access is 'Attendee (temp)' AND it finds a record for the person in the Attendance table for the current EventID. I have a command button near the attendee comboboxes to manually add a temp person to the event. When a temp person is added, it writes their name\access to the Security table (if not already exists) and writes and Attendance record with the current eventid and their security id. They should now appear when I navigate to this record, but not in any records where they were not manually added.

SELECT Q1.Security.ID AS EmpID, Q1.Security.FirstName & " " & Q1.Security.LastName AS EmployeeName

FROM (SELECT Security.ID, Security.FirstName, Security.LastName FROM Events, Security, Attendance WHERE ((((Attendance.EmployeeID=Security.ID) AND (txtEventID = Attendance.EventID) AND (Security.Access="Attendee (temp)")) OR (Security.Access IN ("Attendee","Scheduler","Admin"))))) AS Q1

GROUP BY Q1.Security.ID, Q1.Security.FirstName & " " & Q1.Security.LastName
ORDER BY Q1.Security.FirstName & " " & Q1.Security.LastName;

[June7]

If tables have a 1-to-many or many-to-many relationship, duplication of names in this query is expected without the GROUP BY.

It works if you get the desired result quickly.

An alternative might be:

SELECT ID AS EmpID, FirstName & " " & LastName AS EmployeeName FROM Security WHERE ID IN

(SELECT ID FROM Events, Security, Attendance WHERE (EmployeeID=Security.ID AND txtEventID = EventID AND Access="Attendee (temp)") OR Access IN "Attendee","Scheduler","Admin")

ORDER BY FirstName, LastName;


Might be better performance if used JOIN clauses instead of the WHERE to link key fields. Create the links in query designer by clicking and dragging between fields.