I frequently get the error for trying to use a field that is not part of the aggregate function. Is this because I am selecting a field and not including it in my GROUP BY? Also, I'd prefer to not concatenate the full-name within my queries, is it possible to keep them separate and then concatenate in a textbox in the report. I could not get it to work properly so I did in in the query. It would save me some time if I am asked to sort by last name.
Here's the final queries, using ID to join, as you suggested. Does it look correct?
Code:
SELECT tblPeople.ID, [FirstName] & " " & [LastName] AS FullName, Count(tblPeople.ID) AS CountAll, Sum(Q1.Mon) AS CountMonday
FROM (SELECT tblPeople.ID, tblPeople.FirstName, tblPeople.LastName, tblPeople.Role, IIf(Weekday([tblEvents.EventDate])=2,1,0) AS Mon FROM tblEvents, tblPeople, tblAttendance WHERE ((tblAttendance.EmployeeID=tblPeople.ID) AND (tblEvents.ID = tblAttendance.EventID) AND (tblPeople.Role="Attendee") AND ([tblEvents].[EventDate] BETWEEN #01/01/2013# AND #01/01/2015#))) AS Q1
GROUP BY tblPeople.ID, [FirstName] & " " & [LastName];
Code:
SELECT tblPeople.FirstName & " " & tblPeople.LastName AS FullName, qryCounts.CountAll, qryCounts.CountMonday
FROM qryCounts RIGHT JOIN tblPeople ON qryCounts.ID = tblPeople.ID
ORDER BY qryCounts.CountAll DESC , tblPeople.FirstName & " " & tblPeople.LastName;