Query is blank if one field does not have data...

  • Thread starter MichelleM via AccessMonster.com
  • Start date
M

MichelleM via AccessMonster.com

I have a query that pulls data from several tables, if "CompleteAdherence.
Adherence" does not have any data for the parameters I enter, nothing comes
back. The Adherence field is averaged, so I cannot enter a "0", and since
these are numbers in calculations, I cannot change the field to text to enter
a "NA", otherwise it will not average in the report. Here is my query in SQL,
any insights or suggestions would be much appreciated! Thanks!

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT tbldbo_vw_Agent_Stats_New.DATEDT, CompleteAdherence.Date, tblAgents.
[First Name], tblAgents.[Last Name], CompleteAdherence.Adherence,
tbldbo_vw_Agent_Stats_New.AnsweredACDCallCount, tbldbo_vw_Agent_Stats_New.
CallTalkTime, tbldbo_vw_Agent_Stats_New.InboundHoldTime,
tbldbo_vw_Agent_Stats_New.TrueFollowupTime, [CallTalkTime]+[InboundHoldTime]+
[TrueFollowupTime] AS InboundHandleTime, tbldbo_vw_Agent_Stats_New.
OutboundCount, tbldbo_vw_Agent_Stats_New.OutboundTime,
tbldbo_vw_Agent_Stats_New.OutboundHoldTime, tbldbo_vw_Agent_Stats_New.
OutboundCallFollowup, [OutboundTime]+[OutboundCallFollowup]+[OutboundHoldTime]
AS OutboundHandleTime, tbldbo_vw_Agent_Stats_New.AnsweredACDOtherMediaCount,
tbldbo_vw_Agent_Stats_New.OtherMediaHoldTime, tbldbo_vw_Agent_Stats_New.
OtherMediaTalkTime, tbldbo_vw_Agent_Stats_New.OtherMediaACWTime,
[OtherMediaACWTime]+[OtherMediaTalkTime]+[OtherMediaHoldTime] AS
OtherMediaHandleTime, tbldbo_vw_Agent_Stats_New.TransferTo3922,
tbldbo_vw_Agent_Stats_New.AwayStatusDuration, tbldbo_vw_Agent_Stats_New.
OtherStatusDuration, tblAgents.[Workgroup Leader], tblWGLs.[WGL ID],
tbldbo_vw_Agent_Stats_New.RecordingScoreCalls, tbldbo_vw_Agent_Stats_New.
RecordingCountCalls, tbldbo_vw_Agent_Stats_New.RecordingScoreEmails,
tbldbo_vw_Agent_Stats_New.RecordingCountEmails, tbldbo_vw_Agent_Stats_New.
RecordingCountMonthCalls, tbldbo_vw_Agent_Stats_New.RecordingScoreMonthCalls,
tbldbo_vw_Agent_Stats_New.RecordingCountMonthEmails,
tbldbo_vw_Agent_Stats_New.RecordingScoreMonthEmails, tblAgents.[InB Follow Up
Goal], tblAgents.[OB Follow Up Goal]
FROM (tblWGLs INNER JOIN (tblAgents INNER JOIN tbldbo_vw_Agent_Stats_New ON
tblAgents.[Login ID]=tbldbo_vw_Agent_Stats_New.CNAME) ON tblWGLs.[Workgroup
Leader]=tblAgents.[Workgroup Leader]) INNER JOIN CompleteAdherence ON
(tblAgents.[Login ID]=CompleteAdherence.[Login ID]) AND (CompleteAdherence.
Date=CDATE(tbldbo_vw_Agent_Stats_New.DATEDT))
WHERE (((CDate([DATEDT]))>=[Start Date] And (CDate([DATEDT]))<=[End Date])
AND ((CompleteAdherence.Date)>=[Start Date] And (CompleteAdherence.Date)<=
[End Date]))
GROUP BY tbldbo_vw_Agent_Stats_New.DATEDT, CompleteAdherence.Date, tblAgents.
[First Name], tblAgents.[Last Name], CompleteAdherence.Adherence,
tbldbo_vw_Agent_Stats_New.AnsweredACDCallCount, tbldbo_vw_Agent_Stats_New.
CallTalkTime, tbldbo_vw_Agent_Stats_New.InboundHoldTime,
tbldbo_vw_Agent_Stats_New.TrueFollowupTime, [CallTalkTime]+[InboundHoldTime]+
[TrueFollowupTime], tbldbo_vw_Agent_Stats_New.OutboundCount,
tbldbo_vw_Agent_Stats_New.OutboundTime, tbldbo_vw_Agent_Stats_New.
OutboundHoldTime, tbldbo_vw_Agent_Stats_New.OutboundCallFollowup,
[OutboundTime]+[OutboundCallFollowup]+[OutboundHoldTime],
tbldbo_vw_Agent_Stats_New.AnsweredACDOtherMediaCount,
tbldbo_vw_Agent_Stats_New.OtherMediaHoldTime, tbldbo_vw_Agent_Stats_New.
OtherMediaTalkTime, tbldbo_vw_Agent_Stats_New.OtherMediaACWTime,
[OtherMediaACWTime]+[OtherMediaTalkTime]+[OtherMediaHoldTime],
tbldbo_vw_Agent_Stats_New.TransferTo3922, tbldbo_vw_Agent_Stats_New.
AwayStatusDuration, tbldbo_vw_Agent_Stats_New.OtherStatusDuration, tblAgents.
[Workgroup Leader], tblWGLs.[WGL ID], tbldbo_vw_Agent_Stats_New.
RecordingScoreCalls, tbldbo_vw_Agent_Stats_New.RecordingCountCalls,
tbldbo_vw_Agent_Stats_New.RecordingScoreEmails, tbldbo_vw_Agent_Stats_New.
RecordingCountEmails, tbldbo_vw_Agent_Stats_New.RecordingCountMonthCalls,
tbldbo_vw_Agent_Stats_New.RecordingScoreMonthCalls, tbldbo_vw_Agent_Stats_New.
RecordingCountMonthEmails, tbldbo_vw_Agent_Stats_New.
RecordingScoreMonthEmails, tblAgents.[InB Follow Up Goal], tblAgents.[OB
Follow Up Goal], tbldbo_vw_Agent_Stats_New.CNAME, tblAgents.[Login ID]
HAVING (((tblWGLs.[WGL ID])=[Enter WGL ID]));
 
K

KARL DEWEY

Try changing the FROM to this --
FROM (tblWGLs INNER JOIN (tblAgents INNER JOIN tbldbo_vw_Agent_Stats_New ON
tblAgents.[Login ID]=tbldbo_vw_Agent_Stats_New.CNAME) ON tblWGLs.[Workgroup
Leader]=tblAgents.[Workgroup Leader]) LEFT JOIN CompleteAdherence ON
(tblAgents.[Login ID] =CompleteAdherence.[Login ID]) AND
(CompleteAdherence.Date =CDATE(tbldbo_vw_Agent_Stats_New.DATEDT))

The LEFT join allows all records on the left even if no records on the right.

MichelleM via AccessMonster.com said:
I should have been a bit more specific with what I want to see, if there is
other data for a date, such as 5/24/09, but no adherence, I will want to see
that other data, with the Adherence field blank. Thanks!
I have a query that pulls data from several tables, if "CompleteAdherence.
Adherence" does not have any data for the parameters I enter, nothing comes
back. The Adherence field is averaged, so I cannot enter a "0", and since
these are numbers in calculations, I cannot change the field to text to enter
a "NA", otherwise it will not average in the report. Here is my query in SQL,
any insights or suggestions would be much appreciated! Thanks!

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT tbldbo_vw_Agent_Stats_New.DATEDT, CompleteAdherence.Date, tblAgents.
[First Name], tblAgents.[Last Name], CompleteAdherence.Adherence,
tbldbo_vw_Agent_Stats_New.AnsweredACDCallCount, tbldbo_vw_Agent_Stats_New.
CallTalkTime, tbldbo_vw_Agent_Stats_New.InboundHoldTime,
tbldbo_vw_Agent_Stats_New.TrueFollowupTime, [CallTalkTime]+[InboundHoldTime]+
[TrueFollowupTime] AS InboundHandleTime, tbldbo_vw_Agent_Stats_New.
OutboundCount, tbldbo_vw_Agent_Stats_New.OutboundTime,
tbldbo_vw_Agent_Stats_New.OutboundHoldTime, tbldbo_vw_Agent_Stats_New.
OutboundCallFollowup, [OutboundTime]+[OutboundCallFollowup]+[OutboundHoldTime]
AS OutboundHandleTime, tbldbo_vw_Agent_Stats_New.AnsweredACDOtherMediaCount,
tbldbo_vw_Agent_Stats_New.OtherMediaHoldTime, tbldbo_vw_Agent_Stats_New.
OtherMediaTalkTime, tbldbo_vw_Agent_Stats_New.OtherMediaACWTime,
[OtherMediaACWTime]+[OtherMediaTalkTime]+[OtherMediaHoldTime] AS
OtherMediaHandleTime, tbldbo_vw_Agent_Stats_New.TransferTo3922,
tbldbo_vw_Agent_Stats_New.AwayStatusDuration, tbldbo_vw_Agent_Stats_New.
OtherStatusDuration, tblAgents.[Workgroup Leader], tblWGLs.[WGL ID],
tbldbo_vw_Agent_Stats_New.RecordingScoreCalls, tbldbo_vw_Agent_Stats_New.
RecordingCountCalls, tbldbo_vw_Agent_Stats_New.RecordingScoreEmails,
tbldbo_vw_Agent_Stats_New.RecordingCountEmails, tbldbo_vw_Agent_Stats_New.
RecordingCountMonthCalls, tbldbo_vw_Agent_Stats_New.RecordingScoreMonthCalls,
tbldbo_vw_Agent_Stats_New.RecordingCountMonthEmails,
tbldbo_vw_Agent_Stats_New.RecordingScoreMonthEmails, tblAgents.[InB Follow Up
Goal], tblAgents.[OB Follow Up Goal]
FROM (tblWGLs INNER JOIN (tblAgents INNER JOIN tbldbo_vw_Agent_Stats_New ON
tblAgents.[Login ID]=tbldbo_vw_Agent_Stats_New.CNAME) ON tblWGLs.[Workgroup
Leader]=tblAgents.[Workgroup Leader]) INNER JOIN CompleteAdherence ON
(tblAgents.[Login ID]=CompleteAdherence.[Login ID]) AND (CompleteAdherence.
Date=CDATE(tbldbo_vw_Agent_Stats_New.DATEDT))
WHERE (((CDate([DATEDT]))>=[Start Date] And (CDate([DATEDT]))<=[End Date])
AND ((CompleteAdherence.Date)>=[Start Date] And (CompleteAdherence.Date)<=
[End Date]))
GROUP BY tbldbo_vw_Agent_Stats_New.DATEDT, CompleteAdherence.Date, tblAgents.
[First Name], tblAgents.[Last Name], CompleteAdherence.Adherence,
tbldbo_vw_Agent_Stats_New.AnsweredACDCallCount, tbldbo_vw_Agent_Stats_New.
CallTalkTime, tbldbo_vw_Agent_Stats_New.InboundHoldTime,
tbldbo_vw_Agent_Stats_New.TrueFollowupTime, [CallTalkTime]+[InboundHoldTime]+
[TrueFollowupTime], tbldbo_vw_Agent_Stats_New.OutboundCount,
tbldbo_vw_Agent_Stats_New.OutboundTime, tbldbo_vw_Agent_Stats_New.
OutboundHoldTime, tbldbo_vw_Agent_Stats_New.OutboundCallFollowup,
[OutboundTime]+[OutboundCallFollowup]+[OutboundHoldTime],
tbldbo_vw_Agent_Stats_New.AnsweredACDOtherMediaCount,
tbldbo_vw_Agent_Stats_New.OtherMediaHoldTime, tbldbo_vw_Agent_Stats_New.
OtherMediaTalkTime, tbldbo_vw_Agent_Stats_New.OtherMediaACWTime,
[OtherMediaACWTime]+[OtherMediaTalkTime]+[OtherMediaHoldTime],
tbldbo_vw_Agent_Stats_New.TransferTo3922, tbldbo_vw_Agent_Stats_New.
AwayStatusDuration, tbldbo_vw_Agent_Stats_New.OtherStatusDuration, tblAgents.
[Workgroup Leader], tblWGLs.[WGL ID], tbldbo_vw_Agent_Stats_New.
RecordingScoreCalls, tbldbo_vw_Agent_Stats_New.RecordingCountCalls,
tbldbo_vw_Agent_Stats_New.RecordingScoreEmails, tbldbo_vw_Agent_Stats_New.
RecordingCountEmails, tbldbo_vw_Agent_Stats_New.RecordingCountMonthCalls,
tbldbo_vw_Agent_Stats_New.RecordingScoreMonthCalls, tbldbo_vw_Agent_Stats_New.
RecordingCountMonthEmails, tbldbo_vw_Agent_Stats_New.
RecordingScoreMonthEmails, tblAgents.[InB Follow Up Goal], tblAgents.[OB
Follow Up Goal], tbldbo_vw_Agent_Stats_New.CNAME, tblAgents.[Login ID]
HAVING (((tblWGLs.[WGL ID])=[Enter WGL ID]));
Click to expand...
Click to expand...
 
M

MichelleM via AccessMonster.com

I receive a "join expression not supported" error. : ( Any other ideas?

KARL said:
Try changing the FROM to this --
FROM (tblWGLs INNER JOIN (tblAgents INNER JOIN tbldbo_vw_Agent_Stats_New ON
tblAgents.[Login ID]=tbldbo_vw_Agent_Stats_New.CNAME) ON tblWGLs.[Workgroup
Leader]=tblAgents.[Workgroup Leader]) LEFT JOIN CompleteAdherence ON
(tblAgents.[Login ID] =CompleteAdherence.[Login ID]) AND
(CompleteAdherence.Date =CDATE(tbldbo_vw_Agent_Stats_New.DATEDT))

The LEFT join allows all records on the left even if no records on the right.
I should have been a bit more specific with what I want to see, if there is
other data for a date, such as 5/24/09, but no adherence, I will want to see
Click to expand...
[quoted text clipped - 63 lines]
Follow Up Goal], tbldbo_vw_Agent_Stats_New.CNAME, tblAgents.[Login ID]
HAVING (((tblWGLs.[WGL ID])=[Enter WGL ID]));
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I did not create your tables and query but just took a guess.
Ok, put it back like it was, open in design view.
Your parameters are for dates in CompleteAdherence table, that is why I
suggested a left join.
Your query does nothing with the Adherence field.

Try replacing this part of your query --
Nz([CallTalkTime],0) +Nz([InboundHoldTime],0) +Nz([TrueFollowupTime],0) AS
InboundHandleTime, tbldbo_vw_Agent_Stats_New.OutboundCount,
tbldbo_vw_Agent_Stats_New.OutboundTime,
tbldbo_vw_Agent_Stats_New.OutboundHoldTime,
tbldbo_vw_Agent_Stats_New.OutboundCallFollowup, Nz([OutboundTime],0)
+Nz([OutboundCallFollowup],0) +Nz([OutboundHoldTime],0) AS
OutboundHandleTime, tbldbo_vw_Agent_Stats_New.AnsweredACDOtherMediaCount,
tbldbo_vw_Agent_Stats_New.OtherMediaHoldTime,
tbldbo_vw_Agent_Stats_New.OtherMediaTalkTime,
tbldbo_vw_Agent_Stats_New.OtherMediaACWTime, Nz([OtherMediaACWTime],0)
+Nz([OtherMediaTalkTime],0) +Nz([OtherMediaHoldTime],0) AS
OtherMediaHandleTime,



MichelleM via AccessMonster.com said:
I receive a "join expression not supported" error. : ( Any other ideas?

KARL said:
Try changing the FROM to this --
FROM (tblWGLs INNER JOIN (tblAgents INNER JOIN tbldbo_vw_Agent_Stats_New ON
tblAgents.[Login ID]=tbldbo_vw_Agent_Stats_New.CNAME) ON tblWGLs.[Workgroup
Leader]=tblAgents.[Workgroup Leader]) LEFT JOIN CompleteAdherence ON
(tblAgents.[Login ID] =CompleteAdherence.[Login ID]) AND
(CompleteAdherence.Date =CDATE(tbldbo_vw_Agent_Stats_New.DATEDT))

The LEFT join allows all records on the left even if no records on the right.
I should have been a bit more specific with what I want to see, if there is
other data for a date, such as 5/24/09, but no adherence, I will want to see
Click to expand...
[quoted text clipped - 63 lines]
Follow Up Goal], tbldbo_vw_Agent_Stats_New.CNAME, tblAgents.[Login ID]
HAVING (((tblWGLs.[WGL ID])=[Enter WGL ID]));
Click to expand...
Click to expand...
Click to expand...
 
K

KenSheridan via AccessMonster.com

Firstly, why are you grouping the query? You don't seem to be aggregating
any values.

Because the Date column from the CompleteAdherence table is used in the JOIN,
if the values in that column do not satisfy the parameters no rows will be
returned. You cannot use an OUTER JOIN, however, because you are also
restricting the results by parameters on the Date column in the
CompleteAdherence table. I think you'll probably find the easiest solution
is to break the query down into several queries, firstly on the
CompleteAdherence table, in which you restrict it on the date parameters;
then in a second query join the other tables and similarly restrict them by
the date parameters. Finally LEFT OUTER JOIN the second query to the first
query and use this third query as the report's RecordSource.

To avoid having to respond more than once to each parameter make the
parameters reverences to controls on a dialogue form and open the report from
a button this form after entering the date values in the controls.

Ken Sheridan
Stafford, England
I have a query that pulls data from several tables, if "CompleteAdherence.
Adherence" does not have any data for the parameters I enter, nothing comes
back. The Adherence field is averaged, so I cannot enter a "0", and since
these are numbers in calculations, I cannot change the field to text to enter
a "NA", otherwise it will not average in the report. Here is my query in SQL,
any insights or suggestions would be much appreciated! Thanks!

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT tbldbo_vw_Agent_Stats_New.DATEDT, CompleteAdherence.Date, tblAgents.
[First Name], tblAgents.[Last Name], CompleteAdherence.Adherence,
tbldbo_vw_Agent_Stats_New.AnsweredACDCallCount, tbldbo_vw_Agent_Stats_New.
CallTalkTime, tbldbo_vw_Agent_Stats_New.InboundHoldTime,
tbldbo_vw_Agent_Stats_New.TrueFollowupTime, [CallTalkTime]+[InboundHoldTime]+
[TrueFollowupTime] AS InboundHandleTime, tbldbo_vw_Agent_Stats_New.
OutboundCount, tbldbo_vw_Agent_Stats_New.OutboundTime,
tbldbo_vw_Agent_Stats_New.OutboundHoldTime, tbldbo_vw_Agent_Stats_New.
OutboundCallFollowup, [OutboundTime]+[OutboundCallFollowup]+[OutboundHoldTime]
AS OutboundHandleTime, tbldbo_vw_Agent_Stats_New.AnsweredACDOtherMediaCount,
tbldbo_vw_Agent_Stats_New.OtherMediaHoldTime, tbldbo_vw_Agent_Stats_New.
OtherMediaTalkTime, tbldbo_vw_Agent_Stats_New.OtherMediaACWTime,
[OtherMediaACWTime]+[OtherMediaTalkTime]+[OtherMediaHoldTime] AS
OtherMediaHandleTime, tbldbo_vw_Agent_Stats_New.TransferTo3922,
tbldbo_vw_Agent_Stats_New.AwayStatusDuration, tbldbo_vw_Agent_Stats_New.
OtherStatusDuration, tblAgents.[Workgroup Leader], tblWGLs.[WGL ID],
tbldbo_vw_Agent_Stats_New.RecordingScoreCalls, tbldbo_vw_Agent_Stats_New.
RecordingCountCalls, tbldbo_vw_Agent_Stats_New.RecordingScoreEmails,
tbldbo_vw_Agent_Stats_New.RecordingCountEmails, tbldbo_vw_Agent_Stats_New.
RecordingCountMonthCalls, tbldbo_vw_Agent_Stats_New.RecordingScoreMonthCalls,
tbldbo_vw_Agent_Stats_New.RecordingCountMonthEmails,
tbldbo_vw_Agent_Stats_New.RecordingScoreMonthEmails, tblAgents.[InB Follow Up
Goal], tblAgents.[OB Follow Up Goal]
FROM (tblWGLs INNER JOIN (tblAgents INNER JOIN tbldbo_vw_Agent_Stats_New ON
tblAgents.[Login ID]=tbldbo_vw_Agent_Stats_New.CNAME) ON tblWGLs.[Workgroup
Leader]=tblAgents.[Workgroup Leader]) INNER JOIN CompleteAdherence ON
(tblAgents.[Login ID]=CompleteAdherence.[Login ID]) AND (CompleteAdherence.
Date=CDATE(tbldbo_vw_Agent_Stats_New.DATEDT))
WHERE (((CDate([DATEDT]))>=[Start Date] And (CDate([DATEDT]))<=[End Date])
AND ((CompleteAdherence.Date)>=[Start Date] And (CompleteAdherence.Date)<=
[End Date]))
GROUP BY tbldbo_vw_Agent_Stats_New.DATEDT, CompleteAdherence.Date, tblAgents.
[First Name], tblAgents.[Last Name], CompleteAdherence.Adherence,
tbldbo_vw_Agent_Stats_New.AnsweredACDCallCount, tbldbo_vw_Agent_Stats_New.
CallTalkTime, tbldbo_vw_Agent_Stats_New.InboundHoldTime,
tbldbo_vw_Agent_Stats_New.TrueFollowupTime, [CallTalkTime]+[InboundHoldTime]+
[TrueFollowupTime], tbldbo_vw_Agent_Stats_New.OutboundCount,
tbldbo_vw_Agent_Stats_New.OutboundTime, tbldbo_vw_Agent_Stats_New.
OutboundHoldTime, tbldbo_vw_Agent_Stats_New.OutboundCallFollowup,
[OutboundTime]+[OutboundCallFollowup]+[OutboundHoldTime],
tbldbo_vw_Agent_Stats_New.AnsweredACDOtherMediaCount,
tbldbo_vw_Agent_Stats_New.OtherMediaHoldTime, tbldbo_vw_Agent_Stats_New.
OtherMediaTalkTime, tbldbo_vw_Agent_Stats_New.OtherMediaACWTime,
[OtherMediaACWTime]+[OtherMediaTalkTime]+[OtherMediaHoldTime],
tbldbo_vw_Agent_Stats_New.TransferTo3922, tbldbo_vw_Agent_Stats_New.
AwayStatusDuration, tbldbo_vw_Agent_Stats_New.OtherStatusDuration, tblAgents.
[Workgroup Leader], tblWGLs.[WGL ID], tbldbo_vw_Agent_Stats_New.
RecordingScoreCalls, tbldbo_vw_Agent_Stats_New.RecordingCountCalls,
tbldbo_vw_Agent_Stats_New.RecordingScoreEmails, tbldbo_vw_Agent_Stats_New.
RecordingCountEmails, tbldbo_vw_Agent_Stats_New.RecordingCountMonthCalls,
tbldbo_vw_Agent_Stats_New.RecordingScoreMonthCalls, tbldbo_vw_Agent_Stats_New.
RecordingCountMonthEmails, tbldbo_vw_Agent_Stats_New.
RecordingScoreMonthEmails, tblAgents.[InB Follow Up Goal], tblAgents.[OB
Follow Up Goal], tbldbo_vw_Agent_Stats_New.CNAME, tblAgents.[Login ID]
HAVING (((tblWGLs.[WGL ID])=[Enter WGL ID]));
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Parameters Not Working 2
Trouble with Date field when combining 2 tables 3

Top