Page 5 of 5 FirstFirst 12345
Results 61 to 67 of 67
  1. #61
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Include employee ID in the qryCounts. Join that query to tblPeople.

    SELECT tblPeople.ID, tblPeople.FirstName, tblPeople.LastName, tblPeople.Role, qryCounts.CountAttendance, qryCounts.CountMon


    FROM tblPeople LEFT JOIN qryCounts ON tblPeople.ID = qryCounts.ID;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #62
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    I figured this out this afternoon, please let me know if it looks ok.

    Query 1 (referenced by query 2)
    Code:
    SELECT [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 [FirstName] & " " & [LastName];
    Query 2 (referenced by report)
    Code:
    SELECT tblPeople.ID, tblPeople.FirstName & " " & tblPeople.LastName AS FullName, qryCounts.CountAll, qryCounts.CountMonday
    FROM qryCounts RIGHT JOIN tblPeople ON (qryCounts.FullName) = (tblPeople.FirstName & " " & tblPeople.LastName)
    ORDER BY qryCounts.CountAll DESC , tblPeople.FirstName & " " & tblPeople.LastName;

  3. #63
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Joining on ID would be better but what you have should work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #64
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    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;

  5. #65
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That is the reason.

    The queries seem to work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #66
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    I edited my post, please read what I added. And thanks again for all of your help in all of my threads. If you ever need VB\VBA\Pawn\C help I am at your disposal.

  7. #67
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Do not have to concatenate names in query. Don't even need the names in the GROUP BY query if the employee ID is used. Just pull in the names in the final join.

    If you do concatenate and want to use that as sort field, then: Lastname & ", " & Firstname
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 5 of 5 FirstFirst 12345
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-27-2013, 05:24 PM
  2. Replies: 9
    Last Post: 05-08-2013, 02:37 PM
  3. Replies: 1
    Last Post: 06-08-2012, 11:14 AM
  4. combo box query based on different query result?
    By mejia.j88 in forum Queries
    Replies: 10
    Last Post: 02-15-2012, 02:00 PM
  5. Query result based upon two field conditions
    By diane802 in forum Access
    Replies: 35
    Last Post: 01-08-2010, 06:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums