Query too complex? Access 2003

[Paul (ESI)]

We are using Access 2003. We have a table that contains for reviews on our
employees. It has several fields representing the different questions on our
review sheet that our QA team uses. Each field is populated by "y" for yes,
"n" for no, or "n/a" for not applicable. A query coming from that table then
converts each y to a 1 uses this to total how many y's are in each row of the
table. It then does the same thing to total how many n's are in each row of
the table. This is then used to get a score for each row.

The next query is then used to average everything. It gets and average score
for each employee for a time frame we supply. It also sums one field, which
is populated with either a 0 or 1 for each row. 1 Means there was some kind
of problem with the employee's performance on that row, 0 means there were
none. In this second query, we keep getting an error message that the query
is too complex, and this could maybe be fixed by assigning subtitles to some
of the fields (in other words, instead of doing an equation, do the equation
elsewhere, give it a name like AvgYs, then use that). The problem is we
actually ARE doing this. We are only doing averages or sums, a very simple
equation, and we are doing it by changing "Group by" to "Avg" or "Sum" as
appropriate.

The strangest thing is, doing most monthly time frames causes no trouble. We
run into problems, however, doing the months of October and December 2005.
That is when we get the error message. I cannot think of how else to explain
the issue, so I apologize if this is too vague. If you can help, I'll be
extremely thankful. Please let me know if I can help by providing any further
details, and I will do so.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[Vincent Johns]

I hope my answer doesn't discourage someone else from replying, but what
would help me is a minimal set that produces your problem:

- SQL of each of the Queries involved

- As small a set of records in the Tables that the Queries invoke as
will cause the error message to appear. It would help also if you could
replace any personal data with sanitized substitutes (remember, the
entire world can read these messages).

You may find that, as you prepare the minimal set, you can discover what
the problem is. But if not, I shall be happy to check it out.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Paul (ESI)]

Thanks for your help. WOAH! I just noticed that the SQL for the first query
comes out HUGE. There is so much in there. It runs fine, though. It is the
query that comes next that will not run, but only for certain months. Do you
think, though, that the fact that the first query itself if so gigantic could
be the problem? I've been thinking about it, and we can get rid of A LOT by
getting rid of the calculations of n/a answers. For the sake of our scores,
we only actually use y and n answers, ignoring the n/a completely, so there
is no need for us to even calculate total n/a, and removing that would
eliminate A LOT. Do you think that would help? If not, I'll paste the SQL of
the first query anyway, but it is HUGE, so I'm hoping I won't have to. Here
is the SQL for the second query, which is a query of the first (the huge)
query:

SELECT qryMANUAL_AgentScoresWithTL.agent,
Sum(qryMANUAL_AgentScoresWithTL.CountOfObs) AS SumOfCountOfObs,
Avg(qryMANUAL_AgentScoresWithTL.AverageScore) AS AvgOfAverageScore,
Sum(qryMANUAL_AgentScoresWithTL.SvcBrkdwnTotal) AS SumOfSvcBrkdwnTotal
FROM qryMANUAL_AgentScoresWithTL
WHERE (((qryMANUAL_AgentScoresWithTL.EvaluationDate) Between
[Forms]![frmStackRankingResults]![Start Date] And
[Forms]![frmStackRankingResults]![Stop Date]))
GROUP BY qryMANUAL_AgentScoresWithTL.agent;


--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[Vincent Johns]

I'll try to look at this a little later, but in the meantime, if you
could also post the (huge) first SQL, it might help. That way, if I
need it, it will be available. It would especially help if you could
post some example data that cause the problem, though I understand that
you might not be able to do that. (But then I might also not be able to
reproduce your problem and would have to guess at a solution.) I'll
look at your SQL as soon as I can.

(Or, if you wish, you could email me a copy of your database, but then
nobody else would be able to see it to comment on the problem.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Paul (ESI)]

Wow. Well, okay. I warned you. It is pretty gi-normous. I really appreciate
that you are trying to help, though. I'm working on my hunch now, but the
query is so huge that it is taking forever. As far as giving you the data,
I'm a bit confused as to exactly what you'd want. After all, this giant query
below runs fine, it is actually a query summing and averaging this one that
does not work right, and only for certain months. I'm just trying to do
simple sums and averages.

SELECT tblQualitySummary1_01Jul05to17Jan06_final.EvaluationDate,
tblQualitySummary1_01Jul05to17Jan06_final.Eval_id,
tblQualitySummary1_01Jul05to17Jan06_final.phone,
tblQualitySummary1_01Jul05to17Jan06_final.agent,
Count(tblQualitySummary1_01Jul05to17Jan06_final.EvaluationDate) AS
CountOfObs, ([PCAPromotePEYes]+[PatientCareSkillsYes]+[JobKnowledgeYes]) AS
TotalYes, ([PCAPromotePEPoss]+[PatientCareSkillsPoss]+[JobKnowledgePoss]) AS
TotalPoss, IIf([TotalYes]<1,"-",100*([TotalYes]/[TotalPoss])) AS
AverageScore,
IIf([TotalYes]<1,"-",100*(([TotalYes]-[SvcBrkdwnTotal])/[TotalPoss])) AS
AverageScoreWSvcBrkdwns,
Sum(IIf(tblQualitySummary1_01Jul05to17Jan06_final.ServiceBreakdown="Y",1,0))
AS SvcBrkdwnTotal, Sum(IIf(PCAPromotePE="Y",1,0)) AS PCAPromotePEYes,
Sum(IIf(PCAPromotePE="N",1,0)) AS PCAPromotePENo,
Sum(IIf(PCAPromotePE="N/A",1,0)) AS PCAPromotePENA,
([CountOfObs]-[PCAPromotePENA]) AS PCAPromotePEPoss,
IIf([PCAPromotePEPoss]<1,"-",100*([PCAPromotePEYes]/[PCAPromotePEPoss])) AS
PCAPromotePEPct, Sum(IIf(GratitudeState="Y",1,0)) AS GratitudeStateYes,
Sum(IIf(GratitudeState="N",1,0)) AS GratitudeStateNo,
Sum(IIf(GratitudeState="N/A",1,0)) AS GratitudeStateNA,
([CountOfObs]-[GratitudeStateNA]) AS GratitudeStatePoss,
IIf([GratitudeStatePoss]<1,"-",100*([GratitudeStateYes]/[GratitudeStatePoss]))
AS GratitudeStatePct, Sum(IIf(FirstExpDriver="PCA",1,0)) AS
FirstExpDriverPCA, Sum(IIf(FirstExpDriver="PlanBen",1,0)) AS
FirstExpDriverPlanBen, Sum(IIf(FirstExpDriver="PolProc",1,0)) AS
FirstExpDriverPolProc, Sum(IIf(FirstExpDriver="N/A",1,0)) AS
FirstExpDriverNA, Sum(IIf(SecondExpDriver="PCA",1,0)) AS SecondExpDriverPCA,
Sum(IIf(SecondExpDriver="PlanBen",1,0)) AS SecondExpDriverPlanBen,
Sum(IIf(SecondExpDriver="PolProc",1,0)) AS SecondExpDriverPolProc,
Sum(IIf(SecondExpDriver="N/A",1,0)) AS SecondExpDriverNA,
Sum(IIf(CallerExp="Pos",1,0)) AS CallerExpPos, Sum(IIf(CallerExp="Neu",1,0))
AS CallerExpNeu, Sum(IIf(CallerExp="Neg",1,0)) AS CallerExpNeg,
100*([CallerExpPos]/([CallerExpPos]+[CallerExpNeu]+[CallerExpNeg])) AS
CallerExpPosPct,
100*([CallerExpNeu]/([CallerExpPos]+[CallerExpNeu]+[CallerExpNeg])) AS
CallerExpNeuPct,
100*([CallerExpNeg]/([CallerExpPos]+[CallerExpNeu]+[CallerExpNeg])) AS
CallerExpNegPct, Sum(IIf(ToneInflection="Y",1,0)) AS ToneInflectionYes,
Sum(IIf(ToneInflection="N",1,0)) AS ToneInflectionNo,
Sum(IIf(ToneInflection="N/A",1,0)) AS ToneInflectionNA,
([CountOfObs]-[ToneInflectionNA]) AS ToneInflectionPoss,
IIf([ToneInflectionPoss]<1,"-",100*([ToneInflectionYes]/[ToneInflectionPoss]))
AS ToneInflectionPct, Sum(IIf(Volume="Y",1,0)) AS VolumeYes,
Sum(IIf(Volume="N",1,0)) AS VolumeNo, Sum(IIf(Volume="N/A",1,0)) AS VolumeNA,
([CountOfObs]-[VolumeNA]) AS VolumePoss,
IIf([VolumePoss]<1,"-",100*([VolumeYes]/[VolumePoss])) AS VolumePct,
Sum(IIf(Pace="Y",1,0)) AS PaceYes, Sum(IIf(Pace="N",1,0)) AS PaceNo,
Sum(IIf(Pace="N/A",1,0)) AS PaceNA, ([CountOfObs]-[PaceNA]) AS PacePoss,
IIf([PacePoss]<1,"-",100*([PaceYes]/[PacePoss])) AS PacePct,
Sum(IIf(Clarity="Y",1,0)) AS ClarityYes, Sum(IIf(Clarity="N",1,0)) AS
ClarityNo, Sum(IIf(Clarity="N/A",1,0)) AS ClarityNA,
([CountOfObs]-[ClarityNA]) AS ClarityPoss,
IIf([ClarityPoss]<1,"-",100*([ClarityYes]/[ClarityPoss])) AS ClarityPct,
Sum(IIf(ConfidentVoice="Y",1,0)) AS ConfidentVoiceYes,
Sum(IIf(ConfidentVoice="N",1,0)) AS ConfidentVoiceNo,
Sum(IIf(ConfidentVoice="N/A",1,0)) AS ConfidentVoiceNA,
([CountOfObs]-[ConfidentVoiceNA]) AS ConfidentVoicePoss,
IIf([ConfidentVoicePoss]<1,"-",100*([ConfidentVoiceYes]/[ConfidentVoicePoss]))
AS ConfidentVoicePct,
([ToneInflectionYes]+[VolumeYes]+[PaceYes]+[ClarityYes]+[ConfidentVoiceYes])
AS VoicePresentYes,
([ToneInflectionNo]+[VolumeNo]+[PaceNo]+[ClarityNo]+[ConfidentVoiceNo]) AS
VoicePresentNo,
([ToneInflectionNA]+[VolumeNA]+[PaceNA]+[ClarityNA]+[ConfidentVoiceNA]) AS
VoicePresentNA,
([ToneInflectionPoss]+[VolumePoss]+[PacePoss]+[ClarityPoss]+[ConfidentVoicePoss])
AS VoicePresentPoss,
IIf([VoicePresentPoss]<1,"-",100*([VoicePresentYes]/[VoicePresentPoss])) AS
VOICEPRESENTATIONPCT, Sum(IIf(VerbalAcknowledgement="Y",1,0)) AS VerbAckYes,
Sum(IIf(VerbalAcknowledgement="N",1,0)) AS VerbAckNo,
Sum(IIf(VerbalAcknowledgement="N/A",1,0)) AS VerbAckNA,
([CountOfObs]-[VerbAckNA]) AS VerbAckPoss,
IIf([VerbAckPoss]<1,"-",100*([VerbAckYes]/[VerbAckPoss])) AS VerbAckPct,
Sum(IIf(TeamStatements="Y",1,0)) AS TeamStateYes,
Sum(IIf(TeamStatements="N",1,0)) AS TeamStateNo,
Sum(IIf(TeamStatements="N/A",1,0)) AS TeamStateNA,
([CountOfObs]-[TeamStateNA]) AS TeamStatePoss,
IIf([TeamStatePoss]<1,"-",100*([TeamStateYes]/[TeamStatePoss])) AS
TeamStatePct, Sum(IIf(ApologyEmpathy="Y",1,0)) AS ApologyEmpathyYes,
Sum(IIf(ApologyEmpathy="N",1,0)) AS ApologyEmpathyNo,
Sum(IIf(ApologyEmpathy="N/A",1,0)) AS ApologyEmpathyNA,
([CountOfObs]-[ApologyEmpathyNA]) AS ApologyEmpathyPoss,
IIf([ApologyEmpathyPoss]<1,"-",100*([ApologyEmpathyYes]/[ApologyEmpathyPoss]))
AS ApologyEmpathyPct, Sum(IIf(ActivelyEngaged="Y",1,0)) AS ActiveEngageYes,
Sum(IIf(ActivelyEngaged="N",1,0)) AS ActiveEngageNo,
Sum(IIf(ActivelyEngaged="N/A",1,0)) AS ActiveEngageNA,
([CountOfObs]-[ActiveEngageNA]) AS ActiveEngagePoss,
IIf([ActiveEngagePoss]<1,"-",100*([ActiveEngageYes]/[ActiveEngagePoss])) AS
ActiveEngagePct, Sum(IIf(DesireToHelp="Y",1,0)) AS DesireToHelpYes,
Sum(IIf(DesireToHelp="N",1,0)) AS DesireToHelpNo,
Sum(IIf(DesireToHelp="N/A",1,0)) AS DesireToHelpNA,
([CountOfObs]-[DesireToHelpNA]) AS DesireToHelpPoss,
IIf([DesireToHelpPoss]<1,"-",100*([DesireToHelpYes]/[DesireToHelpPoss])) AS
DesireToHelpPct, Sum(IIf(NoInterruption="Y",1,0)) AS NoInterruptionYes,
Sum(IIf(NoInterruption="N",1,0)) AS NoInterruptionNo,
Sum(IIf(NoInterruption="N/A",1,0)) AS NoInterruptionNA,
([CountOfObs]-[NoInterruptionNA]) AS NoInterruptionPoss,
IIf([NoInterruptionPoss]<1,"-",100*([NoInterruptionYes]/[NoInterruptionPoss]))
AS NoInterruptionPct,
([VerbAckYes]+[TeamStateYes]+[ApologyEmpathyYes]+[ActiveEngageYes]+[DesireToHelpYes]+[NoInterruptionYes])
AS PatientAdvocacyYes,
([VerbAckNo]+[TeamStateNo]+[ApologyEmpathyNo]+[ActiveEngageNo]+[DesireToHelpNo]+[NoInterruptionNo])
AS PatientAdvocacyNo,
([VerbAckNA]+[TeamStateNA]+[ApologyEmpathyNA]+[ActiveEngageNA]+[DesireToHelpNA]+[NoInterruptionNA])
AS PatientAdvocacyNA,
([VerbAckPoss]+[TeamStatePoss]+[ApologyEmpathyPoss]+[ActiveEngagePoss]+[DesireToHelpPoss]+[NoInterruptionPoss])
AS PatientAdvocacyPoss,
IIf([PatientAdvocacyPoss]<1,"-",100*([PatientAdvocacyYes]/[PatientAdvocacyPoss]))
AS PATIENTADVOCACYPCT, Sum(IIf(CallManagement="Y",1,0)) AS CallMgmtYes,
Sum(IIf(CallManagement="N",1,0)) AS CallMgmtNo,
Sum(IIf(CallManagement="N/A",1,0)) AS CallMgmtNA, ([CountOfObs]-[CallMgmtNA])
AS CallMgmtPoss, IIf([CallMgmtPoss]<1,"-",100*([CallMgmtYes]/[CallMgmtPoss]))
AS CallMgmtPct, Sum(IIf(DetailedAnswer="Y",1,0)) AS DetailedAnswerYes,
Sum(IIf(DetailedAnswer="N",1,0)) AS DetailedAnswerNo,
Sum(IIf(DetailedAnswer="N/A",1,0)) AS DetailedAnswerNA,
([CountOfObs]-[DetailedAnswerNA]) AS DetailedAnswerPoss,
IIf([DetailedAnswerPoss]<1,"-",100*([DetailedAnswerYes]/[DetailedAnswerPoss]))
AS DetailedAnswerPct, Sum(IIf(CallerUnderstanding="Y",1,0)) AS
CallerUnderstandingYes, Sum(IIf(CallerUnderstanding="N",1,0)) AS
CallerUnderstandingNo, Sum(IIf(CallerUnderstanding="N/A",1,0)) AS
CallerUnderstandingNA, ([CountOfObs]-[CallerUnderstandingNA]) AS
CallerUnderstandingPoss,
IIf([CallerUnderstandingPoss]<1,"-",100*([CallerUnderstandingYes]/[CallerUnderstandingPoss]))
AS CallerUnderstandingPct, Sum(IIf(AppropriateMeasures="Y",1,0)) AS
AppropriateMeasuresYes, Sum(IIf(AppropriateMeasures="N",1,0)) AS
AppropriateMeasuresNo, Sum(IIf(AppropriateMeasures="N/A",1,0)) AS
AppropriateMeasuresNA, ([CountOfObs]-[AppropriateMeasuresNA]) AS
AppropriateMeasuresPoss,
IIf([AppropriateMeasuresPoss]<1,"-",100*([AppropriateMeasuresYes]/[AppropriateMeasuresPoss]))
AS AppropriateMeasuresPct, Sum(IIf(EducatedCaller="Y",1,0)) AS
EducatedCallerYes, Sum(IIf(EducatedCaller="N",1,0)) AS EducatedCallerNo,
Sum(IIf(EducatedCaller="N/A",1,0)) AS EducatedCallerNA,
([CountOfObs]-[EducatedCallerNA]) AS EducatedCallerPoss,
IIf([EducatedCallerPoss]<1,"-",100*([EducatedCallerYes]/[EducatedCallerPoss]))
AS EducatedCallerPct,
([CallMgmtYes]+[DetailedAnswerYes]+[CallerUnderstandingYes]+[AppropriateMeasuresYes]+[EducatedCallerYes])
AS FirstCallResYes,
([CallMgmtNo]+[DetailedAnswerNo]+[CallerUnderstandingNo]+[AppropriateMeasuresNo]+[EducatedCallerNo])
AS FirstCallResNo,
([CallMgmtNA]+[DetailedAnswerNA]+[CallerUnderstandingNA]+[AppropriateMeasuresNA]+[EducatedCallerNA])
AS FirstCallResNA,
([CallMgmtPoss]+[DetailedAnswerPoss]+[CallerUnderstandingPoss]+[AppropriateMeasuresPoss]+[EducatedCallerPoss])
AS FirstCallResPoss,
IIf([FirstCallResPoss]<1,"-",100*([FirstCallResYes]/[FirstCallResPoss])) AS
FIRSTCALLRESOLUTIONPCT, Sum(IIf(CourteousPhrasing="Y",1,0)) AS
CourteousPhrasingYes, Sum(IIf(CourteousPhrasing="N",1,0)) AS
CourteousPhrasingNo, Sum(IIf(CourteousPhrasing="N/A",1,0)) AS
CourteousPhrasingNA, ([CountOfObs]-[CourteousPhrasingNA]) AS
CourteousPhrasingPoss,
IIf([CourteousPhrasingPoss]<1,"-",100*([CourteousPhrasingYes]/[CourteousPhrasingPoss]))
AS CourteousPhrasingPct, Sum(IIf(FormalHold="Y",1,0)) AS FormalHoldYes,
Sum(IIf(FormalHold="N",1,0)) AS FormalHoldNo, Sum(IIf(FormalHold="N/A",1,0))
AS FormalHoldNA, ([CountOfObs]-[FormalHoldNA]) AS FormalHoldPoss,
IIf([FormalHoldPoss]<1,"-",100*([FormalHoldYes]/[FormalHoldPoss])) AS
FormalHoldPct, Sum(IIf(CallSilence="Y",1,0)) AS CallSilenceYes,
Sum(IIf(CallSilence="N",1,0)) AS CallSilenceNo,
Sum(IIf(CallSilence="N/A",1,0)) AS CallSilenceNA,
([CountOfObs]-[CallSilenceNA]) AS CallSilencePoss,
IIf([CallSilencePoss]<1,"-",100*([CallSilenceYes]/[CallSilencePoss])) AS
CallSilencePct, Sum(IIf(ProfessionalCommunication="Y",1,0)) AS ProfCommYes,
Sum(IIf(ProfessionalCommunication="N",1,0)) AS ProfCommNo,
Sum(IIf(ProfessionalCommunication="N/A",1,0)) AS ProfCommNA,
([CountOfObs]-[ProfCommNA]) AS ProfCommPoss,
IIf([ProfCommPoss]<1,"-",100*([ProfCommYes]/[ProfCommPoss])) AS ProfCommPct,
([CourteousPhrasingYes]+[FormalHoldYes]+[CallSilenceYes]+[ProfCommYes]) AS
ProfEtiquetteYes,
([CourteousPhrasingNo]+[FormalHoldNo]+[CallSilenceNo]+[ProfCommNo]) AS
ProfEtiquetteNo,
([CourteousPhrasingNA]+[FormalHoldNA]+[CallSilenceNA]+[ProfCommNA]) AS
ProfEtiquetteNA,
([CourteousPhrasingPoss]+[FormalHoldPoss]+[CallSilencePoss]+[ProfCommPoss])
AS ProfEtiquettePoss,
IIf([ProfEtiquettePoss]<1,"-",100*([ProfEtiquetteYes]/[ProfEtiquettePoss]))
AS PROFESSIONALISMETIQUETTEPCT,
([VoicePresentYes]+[PatientAdvocacyYes]+[FirstCallResYes]+[ProfEtiquetteYes])
AS PatientCareSkillsYes,
([VoicePresentNo]+[PatientAdvocacyNo]+[FirstCallResNo]+[ProfEtiquetteNo]) AS
PatientCareSkillsNo,
([VoicePresentNA]+[PatientAdvocacyNA]+[FirstCallResNA]+[ProfEtiquetteNA]) AS
PatientCareSkillsNA,
([VoicePresentPoss]+[PatientAdvocacyPoss]+[FirstCallResPoss]+[ProfEtiquettePoss])
AS PatientCareSkillsPoss,
IIf([PatientCareSkillsPoss]<1,"-",100*([PatientCareSkillsYes]/[PatientCareSkillsPoss]))
AS PATIENTCARESKILLSPCT, Sum(IIf(VerifiedInformation="Y",1,0)) AS VerInfoYes,
Sum(IIf(VerifiedInformation="N",1,0)) AS VerInfoNo,
Sum(IIf(VerifiedInformation="N/A",1,0)) AS VerInfoNA,
([CountOfObs]-[VerInfoNA]) AS VerInfoPoss,
IIf([VerInfoPoss]<1,"-",100*([VerInfoYes]/[VerInfoPoss])) AS VerInfoPct,
Sum(IIf(CallDocumented="Y",1,0)) AS CallDocYes,
Sum(IIf(CallDocumented="N",1,0)) AS CallDocNo,
Sum(IIf(CallDocumented="N/A",1,0)) AS CallDocNA, ([CountOfObs]-[CallDocNA])
AS CallDocPoss, IIf([CallDocPoss]<1,"-",100*([CallDocYes]/[CallDocPoss])) AS
CallDocPct, Sum(IIf(CallReasons="Y",1,0)) AS CallReasonsYes,
Sum(IIf(CallReasons="N",1,0)) AS CallReasonsNo,
Sum(IIf(CallReasons="N/A",1,0)) AS CallReasonsNA,
([CountOfObs]-[CallReasonsNA]) AS CallReasonsPoss,
IIf([CallReasonsPoss]<1,"-",100*([CallReasonsYes]/[CallReasonsPoss])) AS
CallReasonsPct, Sum(IIf(CallStatus="Y",1,0)) AS CallStatusYes,
Sum(IIf(CallStatus="N",1,0)) AS CallStatusNo, Sum(IIf(CallStatus="N/A",1,0))
AS CallStatusNA, ([CountOfObs]-[CallStatusNA]) AS CallStatusPoss,
IIf([CallStatusPoss]<1,"-",100*([CallStatusYes]/[CallStatusPoss])) AS
CallStatusPct, ([CallDocYes]+[CallReasonsYes]+[CallStatusYes]) AS
CallDocumentationYes, ([CallDocNo]+[CallReasonsNo]+[CallStatusNo]) AS
CallDocumentationNo, ([CallDocNA]+[CallReasonsNA]+[CallStatusNA]) AS
CallDocumentationNA, ([CallDocPoss]+[CallReasonsPoss]+[CallStatusPoss]) AS
CallDocumentationPoss,
IIf([CallDocumentationPoss]<1,"-",100*([CallDocumentationYes]/[CallDocumentationPoss]))
AS CALLDOCUMENTATIONPCT, Sum(IIf(UsedResources="Y",1,0)) AS UsedResourcesYes,
Sum(IIf(UsedResources="N",1,0)) AS UsedResourcesNo,
Sum(IIf(UsedResources="N/A",1,0)) AS UsedResourcesNA,
([CountOfObs]-[UsedResourcesNA]) AS UsedResourcesPoss,
IIf([UsedResourcesPoss]<1,"-",100*([UsedResourcesYes]/[UsedResourcesPoss]))
AS UsedResourcesPct, Sum(IIf(UsedDepartments="Y",1,0)) AS UsedDepartmentsYes,
Sum(IIf(UsedDepartments="N",1,0)) AS UsedDepartmentsNo,
Sum(IIf(UsedDepartments="N/A",1,0)) AS UsedDepartmentsNA,
([CountOfObs]-[UsedDepartmentsNA]) AS UsedDepartmentsPoss,
IIf([UsedDepartmentsPoss]<1,"-",100*([UsedDepartmentsYes]/[UsedDepartmentsPoss]))
AS UsedDepartmentsPct, Sum(IIf(FollowUp="Y",1,0)) AS FollowUpYes,
Sum(IIf(FollowUp="N",1,0)) AS FollowUpNo, Sum(IIf(FollowUp="N/A",1,0)) AS
FollowUpNA, ([CountOfObs]-[FollowUpNA]) AS FollowUpPoss,
IIf([FollowUpPoss]<1,"-",100*([FollowUpYes]/[FollowUpPoss])) AS FollowUpPct,
([UsedResourcesYes]+[UsedDepartmentsYes]+[FollowUpYes]) AS
AppropriateUseResourcesYes,
([UsedResourcesNo]+[UsedDepartmentsNo]+[FollowUpNo]) AS
AppropriateUseResourcesNo,
([UsedResourcesNA]+[UsedDepartmentsNA]+[FollowUpNA]) AS
AppropriateUseResourcesNA,
([UsedResourcesPoss]+[UsedDepartmentsPoss]+[FollowUpPoss]) AS
AppropriateUseResourcesPoss,
IIf([AppropriateUseResourcesPoss]<1,"-",100*([AppropriateUseResourcesYes]/[AppropriateUseResourcesPoss]))
AS APPROPRIATEUSEOFRESOURCESPCT, Sum(IIf(Greeting="Y",1,0)) AS GreetingYes,
Sum(IIf(Greeting="N",1,0)) AS GreetingNo, Sum(IIf(Greeting="N/A",1,0)) AS
GreetingNA, ([CountOfObs]-[GreetingNA]) AS GreetingPoss,
IIf([GreetingPoss]<1,"-",100*([GreetingYes]/[GreetingPoss])) AS GreetingPct,
Sum(IIf(AdditionalAssistance="Y",1,0)) AS AddAssYes,
Sum(IIf(AdditionalAssistance="N",1,0)) AS AddAssNo,
Sum(IIf(AdditionalAssistance="N/A",1,0)) AS AddAssNA,
([CountOfObs]-[AddAssNA]) AS AddAssPoss,
IIf([AddAssPoss]<1,"-",100*([AddAssYes]/[AddAssPoss])) AS AddAssPct,
Sum(IIf(Closing="Y",1,0)) AS ClosingYes, Sum(IIf(Closing="N",1,0)) AS
ClosingNo, Sum(IIf(Closing="N/A",1,0)) AS ClosingNA,
([CountOfObs]-[ClosingNA]) AS ClosingPoss,
IIf([ClosingPoss]<1,"-",100*([ClosingYes]/[ClosingPoss])) AS ClosingPct,
Sum(IIf(Verbiage="Y",1,0)) AS VerbYes, Sum(IIf(Verbiage="N",1,0)) AS VerbNo,
Sum(IIf(Verbiage="N/A",1,0)) AS VerbNA, ([CountOfObs]-[VerbNA]) AS VerbPoss,
IIf([VerbPoss]<1,"-",100*([VerbYes]/[VerbPoss])) AS VerbPct,
([GreetingYes]+[AddAssYes]+[ClosingYes]+[VerbYes]) AS CallScriptingYes,
([GreetingNo]+[AddAssNo]+[ClosingNo]+[VerbNo]) AS CallScriptingNo,
([GreetingNA]+[AddAssNA]+[ClosingNA]+[VerbNA]) AS CallScriptingNA,
([GreetingPoss]+[AddAssPoss]+[ClosingPoss]+[VerbPoss]) AS CallScriptingPoss,
IIf([CallScriptingPoss]<1,"-",100*([CallScriptingYes]/[CallScriptingPoss]))
AS CALLSCRIPTINGPCT,
([VerInfoYes]+[CallDocumentationYes]+[AppropriateUseResourcesYes]+[CallScriptingYes])
AS JobKnowledgeYes,
([VerInfoNo]+[CallDocumentationNo]+[AppropriateUseResourcesNo]+[CallScriptingNo])
AS JobKnowledgeNo,
([VerInfoNA]+[CallDocumentationNA]+[AppropriateUseResourcesNA]+[CallScriptingNA])
AS JobKnowledgeNA,
([VerInfoPoss]+[CallDocumentationPoss]+[AppropriateUseResourcesPoss]+[CallScriptingPoss])
AS JobKnowledgePoss,
IIf([JobKnowledgePoss]<1,"-",100*([JobKnowledgeYes]/[JobKnowledgePoss])) AS
JOBKNOWLEDGEPCT
FROM tblQualitySummary1_01Jul05to17Jan06_final
GROUP BY tblQualitySummary1_01Jul05to17Jan06_final.EvaluationDate,
tblQualitySummary1_01Jul05to17Jan06_final.Eval_id,
tblQualitySummary1_01Jul05to17Jan06_final.phone,
tblQualitySummary1_01Jul05to17Jan06_final.agent
HAVING (((tblQualitySummary1_01Jul05to17Jan06_final.EvaluationDate) Between
[Forms]![frmStackRankingResults]![Start Date] And
[Forms]![frmStackRankingResults]![Stop Date]))
ORDER BY tblQualitySummary1_01Jul05to17Jan06_final.agent;

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

I'll try to look at this a little later, but in the meantime, if you
could also post the (huge) first SQL, it might help. That way, if I
need it, it will be available. It would especially help if you could
post some example data that cause the problem, though I understand that
you might not be able to do that. (But then I might also not be able to
reproduce your problem and would have to guess at a solution.) I'll
look at your SQL as soon as I can.

(Or, if you wish, you could email me a copy of your database, but then
nobody else would be able to see it to comment on the problem.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Thanks for your help. WOAH! I just noticed that the SQL for the first query
comes out HUGE. There is so much in there. It runs fine, though. It is the
query that comes next that will not run, but only for certain months. Do you
think, though, that the fact that the first query itself if so gigantic could
be the problem? I've been thinking about it, and we can get rid of A LOT by
getting rid of the calculations of n/a answers. For the sake of our scores,
we only actually use y and n answers, ignoring the n/a completely, so there
is no need for us to even calculate total n/a, and removing that would
eliminate A LOT. Do you think that would help? If not, I'll paste the SQL of
the first query anyway, but it is HUGE, so I'm hoping I won't have to. Here
is the SQL for the second query, which is a query of the first (the huge)
query:

SELECT qryMANUAL_AgentScoresWithTL.agent,
Sum(qryMANUAL_AgentScoresWithTL.CountOfObs) AS SumOfCountOfObs,
Avg(qryMANUAL_AgentScoresWithTL.AverageScore) AS AvgOfAverageScore,
Sum(qryMANUAL_AgentScoresWithTL.SvcBrkdwnTotal) AS SumOfSvcBrkdwnTotal
FROM qryMANUAL_AgentScoresWithTL
WHERE (((qryMANUAL_AgentScoresWithTL.EvaluationDate) Between
[Forms]![frmStackRankingResults]![Start Date] And
[Forms]![frmStackRankingResults]![Stop Date]))
GROUP BY qryMANUAL_AgentScoresWithTL.agent;

 

[John Vinson]

It is pretty gi-normous.

And it almost certainly is TOO gi-normous for Access to handle. The
"Query Too Complex" error arises when the compiled query exceeds
64KBytes, and it wouldn't surprise me a bit if this monster is that
big or bigger.

Part of the problem is that your table design is very badly
non-normalized, with lots of fields like FirstExpDriver and
SecondExpDriver (a one to many relationship embedded in the record)
and ClarityPoss and ClarityYes (data embedded in fieldnames).

You *might* be able to salvage this by using short (one- or
two-letter) aliases for the tablename and for the fieldnames used in
expressions, but I fear that the major effort of normalizing your
table structure may be needed.

John W. Vinson[MVP]

 

[Paul (ESI)]

I have to tell you, I am so glad to be able to admit that I did not develop
that monster query, or the table from where it originates. Whoever did
apparently was just in a rush to put something together, because it is ugly,
and it is giving me a major headache. I greatly appreciate that you are
keeping with me on this question. You're a saint. I'm having another thought.
I'm trying to think of a way to do this without it taking so long and being
so tedious that it isn't worth it. Let me know if you think this may work:

I'm considering breaking that monster query into several different queries.
There are several different categories, like Job Knowledge, First Call
Resolution, etc. I'm considering breaking each of those into their own
separate query, then combining them all at the end for the final query to get
each agent's final quality score. It will be a bit of a pain, but if it
works, it will probably a quicker, easier transition than trying to redesign
everthing. Do you think this may work?

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[John Vinson]

I have to tell you, I am so glad to be able to admit that I did not develop
that monster query, or the table from where it originates. Whoever did
apparently was just in a rush to put something together, because it is ugly,
and it is giving me a major headache. I greatly appreciate that you are
keeping with me on this question. You're a saint. I'm having another thought.
I'm trying to think of a way to do this without it taking so long and being
so tedious that it isn't worth it. Let me know if you think this may work:

I'm considering breaking that monster query into several different queries.
There are several different categories, like Job Knowledge, First Call
Resolution, etc. I'm considering breaking each of those into their own
separate query, then combining them all at the end for the final query to get
each agent's final quality score. It will be a bit of a pain, but if it
works, it will probably a quicker, easier transition than trying to redesign
everthing. Do you think this may work?

Yes and no. If you try to put it together in one final master query,
you'll still get the QTC error (because Access will have to put
everything together anyhow).

However, you *can* use a Report with several subreports, each based on
one of these partial queries, and do some summarizing and totals
directly on the Report. I'm presuming that's the final desired outcome
anyhow - a Report showing all the information?

In any case if you can offload some of the calculations to a Form or
Report, you can lighten the burden on the Query.


John W. Vinson[MVP]

 

[Vincent Johns]

Paul,

Thanks for posting the actual Query -- it does clarify things, as
John Vinson observed. And no, for now you don't need to post any sample
data.

I suspect that you can nibble away at this (and I'm impressed that you
got any results at all) by looking at some of the fields that you might
be able to off-load to other Queries. And another way to get around the
problem that John mentioned is to take some of your subsets and rewrite
them as Make-Table Queries, run those to create temporary Tables, and
then base your overall Query on those temporary Tables. (And then erase
the temporary ones when you're finished.)

One way to simplify, to pick a random example, is, instead of this...

([CallMgmtNo]+[DetailedAnswerNo]+[CallerUnderstandingNo]
+[AppropriateMeasuresNo]+[EducatedCallerNo]) AS FirstCallResNo

.... to place all of these fields into a Table in which one field
contains the field value ([CallMgmtNo] or [DetailedAnswerNo], etc.),
another field identifies which value it represents, and another (yes/no)
field identifies it as one of those on which [FirstCallResNo] depends.
Then you can define a Query which sums the values, and call that Query
here to produce just the sum, which would be the value of
[FirstCallResNo].

There are many opportunities in this SQL to do that sort of thing.
Perhaps breaking it into simpler parts would help boost your confidence
that the reported results are calculated accurately. (Probably they
already are, but who would know?? How would you convince me, for
example, that this SQL is an accurate reflection of the specifications?
It looks like kind of a bear to audit.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

And it almost certainly is TOO gi-normous for Access to handle. The
"Query Too Complex" error arises when the compiled query exceeds
64KBytes, and it wouldn't surprise me a bit if this monster is that
big or bigger.

Part of the problem is that your table design is very badly
non-normalized, with lots of fields like FirstExpDriver and
SecondExpDriver (a one to many relationship embedded in the record)
and ClarityPoss and ClarityYes (data embedded in fieldnames).

You *might* be able to salvage this by using short (one- or
two-letter) aliases for the tablename and for the fieldnames used in
expressions, but I fear that the major effort of normalizing your
table structure may be needed.

John W. Vinson[MVP]

[...]