Query only works if I have atleast 1 record in table

[BRZ-Ryan]

This query works great...but only if I have atleast 1 record in Attendance table. I'd like it to work even when Attendance is empty (which it will be when I first deploy this to the team). I can populate a dummy record if required but I'd rather not.



Intention is to grab all people with "Attendee","Scheduler","Admin" access AND get "Attendee (temp)" people IF they have a record in the attendance table for the current event.

Attendance table:
ID
EventID
EmployeeID

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 (Forms!Navigation!NavigationSubform.Form!ID = 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;

[mrojas]

Test if there are any records with the DCount function. If value returned is zero, there are no records in table.

[BRZ-Ryan]

You didn't understand my question.. My query only works if 1+ records exist in Attendance table, I need it to work even if 0 records.

[ItsMe]

Perhaps not running the query when there are 0 records is an option.

[BRZ-Ryan]

This query populates a combobox in a continuous form to create records in attendance. I want an explanation as to what I did wrong, not work arounds.

[mrojas]

I should have been more explicit. Do not run your query if there are no records.

[BRZ-Ryan]

Smh.. then how do I get a record in my attendance table?

This combobox needs to be populated so I can select an item to create a record in Attendance table. The above query populates the combobox.

Something is wrong with my query, if you don't know how to help then don't help.

[ItsMe]

This combobox needs to be populated so I can select an item to create a record in Attendance table. The above query populates the combobox.

At the risk of not being any help, this seems to be a pretty good description of what the underlying problem is.