Query Left Join Issue

[msk7777]

I have a query pulling from two queries. I have to be able to pull all the active employee names from the first query and even if they do not have any data for that day in the second query. My query works fine until I add a date parameter that is directed at the second query. When i tell it to pull all the reps data between certain dates I then lose the reps that didn't have data between those dates. If I don't use the parameter all the names come back. I believe this has something to do with the fact that the 1st query is just a list of names with no date field so I have to use the second query to be able to use a date parameter. How can I get around this? Here is the SQL for the query:

SELECT [Active Employee Information Query].Department, [Active Employee Information Query].[Employee First Name], [Active Employee Information Query].[Employee Last Name], Sum(IIf(IsNull([AgentPerformanceTable_All_Totals]![CallsOffered]),0,[AgentPerformanceTable_All_Totals]![CallsOffered])) AS CallsOffered, Sum(IIf(IsNull([AgentPerformanceTable_All_Totals]![CallsAnswered]),0,[AgentPerformanceTable_All_Totals]![CallsAnswered])) AS CallsAnswered, Sum(IIf(IsNull([AgentPerformanceTable_All_Totals]![NotReadyTime]),0,[AgentPerformanceTable_All_Totals]![NotReadyTime])) AS NotReadyTime, Sum(IIf(IsNull([AgentPerformanceTable_All_Totals]![LoggedInTime]),0,[AgentPerformanceTable_All_Totals]![LoggedInTime])) AS LoggedInTime, Sum(IIf(IsNull([AgentPerformanceTable_All_Totals]![RingTime]),0,[AgentPerformanceTable_All_Totals]![RingTime])) AS RingTime, Sum(IIf(IsNull([AgentPerformanceTable_All_Totals]![AvgRingTime]),0,[AgentPerformanceTable_All_Totals]![AvgRingTime])) AS AvgRingTime, Sum(IIf(IsNull([AgentPerformanceTable_All_Totals]![TalkTime]),0,[AgentPerformanceTable_All_Totals]![TalkTime])) AS TalkTime, Sum(IIf(IsNull([AgentPerformanceTable_All_Totals]![AvgTalkTime]),0,[AgentPerformanceTable_All_Totals]![AvgTalkTime])) AS AvgTalkTime, Sum(IIf(IsNull([AgentPerformanceTable_All_Totals]![HoldTime]),0,[AgentPerformanceTable_All_Totals]![HoldTime])) AS HoldTime, Sum(IIf(IsNull([AgentPerformanceTable_All_Totals]![ExternalCalls]),0,[AgentPerformanceTable_All_Totals]![ExternalCalls])) AS ExternalCalls
FROM [Active Employee Information Query] LEFT JOIN AgentPerformanceQueryStep1 ON ([Active Employee Information Query].[Employee Last Name] = AgentPerformanceQueryStep1.AgentLastName) AND ([Active Employee Information Query].[Employee First Name] = AgentPerformanceQueryStep1.AgentFirstName)
WHERE ((([Active Employee Information Query].Department)="Member Services" Or ([Active Employee Information Query].Department)="Physician Relations" Or ([Active Employee Information Query].Department)="Team Lead" Or ([Active Employee Information Query].Department)="Trainer" Or ([Active Employee Information Query].Department)="Management") AND ((AgentPerformanceQueryStep1.DateStamp) Between [Start Date] And [End Date]))
GROUP BY [Active Employee Information Query].Department, [Active Employee Information Query].[Employee First Name], [Active Employee Information Query].[Employee Last Name];

I hope someone can help!

 

[MaZeWorX]

apply the date range first then add that query to your second

 

[MSAccessRookie]

What does [AgentPerformanceTable_All_Totals]! in the Select statements refer to?

 

[msk7777]

apply the date range first then add that query to your second

I'm sorry I'm not quite following you

 

[msk7777]

What does [AgentPerformanceTable_All_Totals]! in the Select statements refer to?

Thanks for that catch, that was wrong I fixed that, but still not working right. Here is the new SQL:

SELECT [Active Employee Information Query].Department, [Active Employee Information Query].[Employee First Name], [Active Employee Information Query].[Employee Last Name], Sum(IIf(IsNull([AgentPerformanceQueryStep1]![CallsOffered]),0,[AgentPerformanceQueryStep1]![CallsOffered])) AS CallsOffered, Sum(IIf(IsNull([AgentPerformanceQueryStep1]![CallsAnswered]),0,[AgentPerformanceQueryStep1]![CallsAnswered])) AS CallsAnswered, Sum(IIf(IsNull([AgentPerformanceQueryStep1]![NotReadyTime]),0,[AgentPerformanceQueryStep1]![NotReadyTime])) AS NotReadyTime, Sum(IIf(IsNull([AgentPerformanceQueryStep1]![LoggedInTime]),0,[AgentPerformanceQueryStep1]![LoggedInTime])) AS LoggedInTime, Sum(IIf(IsNull([AgentPerformanceQueryStep1]![RingTime]),0,[AgentPerformanceQueryStep1]![RingTime])) AS RingTime, Sum(IIf(IsNull([AgentPerformanceQueryStep1]![AvgRingTime]),0,[AgentPerformanceQueryStep1]![AvgRingTime])) AS AvgRingTime, Sum(IIf(IsNull([AgentPerformanceQueryStep1]![TalkTime]),0,[AgentPerformanceQueryStep1]![TalkTime])) AS TalkTime, Sum(IIf(IsNull([AgentPerformanceQueryStep1]![AvgTalkTime]),0,[AgentPerformanceQueryStep1]![AvgTalkTime])) AS AvgTalkTime, Sum(IIf(IsNull([AgentPerformanceQueryStep1]![HoldTime]),0,[AgentPerformanceQueryStep1]![HoldTime])) AS HoldTime, Sum(IIf(IsNull([AgentPerformanceQueryStep1]![ExternalCalls]),0,[AgentPerformanceQueryStep1]![ExternalCalls])) AS ExternalCalls
FROM [Active Employee Information Query] LEFT JOIN AgentPerformanceQueryStep1 ON ([Active Employee Information Query].[Employee Last Name] = AgentPerformanceQueryStep1.AgentLastName) AND ([Active Employee Information Query].[Employee First Name] = AgentPerformanceQueryStep1.AgentFirstName)
WHERE ((([Active Employee Information Query].Department)="Member Services" Or ([Active Employee Information Query].Department)="Physician Relations" Or ([Active Employee Information Query].Department)="Team Lead" Or ([Active Employee Information Query].Department)="Trainer" Or ([Active Employee Information Query].Department)="Management") AND ((AgentPerformanceQueryStep1.DateStamp) Between [Start Date] And [End Date]))
GROUP BY [Active Employee Information Query].Department, [Active Employee Information Query].[Employee First Name], [Active Employee Information Query].[Employee Last Name];

 

[MSAccessRookie]

I reformatted the query as follows:

• Aliases for the SubQuery Names are used to make the Query more readable. (You can always change these back or change them to any other aliases that suit your needs better).
• The Compound Or Statement (which may or may not have worked as you expected), is now an In() Statement making the Where statement a little less complex.

In doing so, it looks like this formula should do what you want, as long as I got all the "(" and ")" right.

Note: This is air code and may need to be tweaked a little bit.

[FONT=Times New Roman][SIZE=3]SELECT [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    AEIQ.Department,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    AEIQ.[Employee First Name],[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    AEIQ.[Employee Last Name],[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Sum(IIf(IsNull(APQS1.[CallsOffered]), 0, APQS1.[CallsOffered])) AS CallsOffered,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Sum(IIf(IsNull(APQS1.[CallsAnswered]), 0, APQS1.[CallsAnswered])) AS CallsAnswered,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Sum(IIf(IsNull(APQS1.[NotReadyTime]), 0 , APQS1.[NotReadyTime])) AS NotReadyTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Sum(IIf(IsNull(APQS1.[LoggedInTime]), 0 , APQS1.[LoggedInTime])) AS LoggedInTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Sum(IIf(IsNull(APQS1.[RingTime]), 0 , APQS1.[RingTime])) AS RingTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Sum(IIf(IsNull(APQS1.[AvgRingTime]), 0 , APQS1.[AvgRingTime])) AS AvgRingTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Sum(IIf(IsNull(APQS1.[TalkTime]), 0 , APQS1.[TalkTime])) AS TalkTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Sum(IIf(IsNull(APQS1.[AvgTalkTime]), 0 , APQS1.[AvgTalkTime])) AS AvgTalkTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Sum(IIf(IsNull(APQS1.[HoldTime]), 0 , APQS1.[HoldTime])) AS HoldTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Sum(IIf(IsNull(APQS1.[ExternalCalls]), 0 , APQS1.[ExternalCalls])) AS ExternalCalls[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]FROM [Active Employee Information Query] As AEIQ [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    LEFT JOIN AgentPerformanceQueryStep1 As APQS1 [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        ON (AEIQ.[Employee Last Name] = APQS1.AgentLastName) AND [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]               (AEIQ.[Employee First Name] = APQS1.AgentFirstName)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]WHERE ((AEIQ.Department In ("Member Services", "Physician Relations", "Team Lead", "Trainer", "Management")) AND ((APQS1.DateStamp) Between [Start Date] And [End Date]))[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]GROUP BY AEIQ.Department, AEIQ.[Employee First Name], AEIQ.[Employee Last Name];[/SIZE][/FONT]

 

[msk7777]

I copied this and it runs the query but I am getting the same results. If a rep who didn't have any data in those dates are being extracted from the query.

I reformatted the query as follows:

• Aliases for the SubQuery Names are used to make the Query more readable. (You can always change these back or change them to any other aliases that suit your needs better).
• The Compound Or Statement (which may or may not have worked as you expected), is now an In() Statement making the Where statement a little less complex.

In doing so, it looks like this formula should do what you want, as long as I got all the "(" and ")" right.

Note: This is air code and may need to be tweaked a little bit.

[FONT=Times New Roman][SIZE=3]SELECT [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]   AEIQ.Department,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   AEIQ.[Employee First Name],[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   AEIQ.[Employee Last Name],[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Sum(IIf(IsNull(APQS1.[CallsOffered]), 0, APQS1.[CallsOffered])) AS CallsOffered,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Sum(IIf(IsNull(APQS1.[CallsAnswered]), 0, APQS1.[CallsAnswered])) AS CallsAnswered,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Sum(IIf(IsNull(APQS1.[NotReadyTime]), 0 , APQS1.[NotReadyTime])) AS NotReadyTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Sum(IIf(IsNull(APQS1.[LoggedInTime]), 0 , APQS1.[LoggedInTime])) AS LoggedInTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Sum(IIf(IsNull(APQS1.[RingTime]), 0 , APQS1.[RingTime])) AS RingTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Sum(IIf(IsNull(APQS1.[AvgRingTime]), 0 , APQS1.[AvgRingTime])) AS AvgRingTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Sum(IIf(IsNull(APQS1.[TalkTime]), 0 , APQS1.[TalkTime])) AS TalkTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Sum(IIf(IsNull(APQS1.[AvgTalkTime]), 0 , APQS1.[AvgTalkTime])) AS AvgTalkTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Sum(IIf(IsNull(APQS1.[HoldTime]), 0 , APQS1.[HoldTime])) AS HoldTime,[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Sum(IIf(IsNull(APQS1.[ExternalCalls]), 0 , APQS1.[ExternalCalls])) AS ExternalCalls[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]FROM [Active Employee Information Query] As AEIQ [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]   LEFT JOIN AgentPerformanceQueryStep1 As APQS1 [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       ON (AEIQ.[Employee Last Name] = APQS1.AgentLastName) AND [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]              (AEIQ.[Employee First Name] = APQS1.AgentFirstName)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]WHERE ((AEIQ.Department In ("Member Services", "Physician Relations", "Team Lead", "Trainer", "Management")) AND ((APQS1.DateStamp) Between [Start Date] And [End Date]))[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]GROUP BY AEIQ.Department, AEIQ.[Employee First Name], AEIQ.[Employee Last Name];[/SIZE][/FONT]