Query calculation error

[skanrey]

In an offline copy of our DB (Access 2000) I built a query to tally certain records per employee and give percentages for metrics evaluation. The string is;
SELECT qryTechMetrics_RawData.[Tech #], Count(qryTechMetrics_RawData.[Install Date]) AS [CountOfInstall Date], Sum(qryTechMetrics_RawData.Points) AS SumOfPoints, Count([Job_Status]="cmp") AS Completed, Count([Job_Status]="cmp")/[CountOfInstall Date] AS [Complete Ratio], Count(qryTechMetrics_RawData.Call_Date) AS CountOfCall_Date, 1-[CountOfCall_Date]/Count([Job_Status]="cmp") AS [TC Ratio], Count([Missed]="Not Null") AS [Total Missed], 1-Count([Missed]="Not Null")/[CountOfInstall Date] AS [Missed Ratio]
FROM qryTechMetrics_RawData
GROUP BY qryTechMetrics_RawData.[Tech #]
ORDER BY qryTechMetrics_RawData.[Tech #];

This worked fine and all calc's were accurate. I imported the query into the running DB and it will not correctly calculate the "Count([Job_Status]="cmp") AS Completed" portion. Instead it is giving me the same # as "[CountOfInstall Date]". It appears to not be addressing the filter for "cmp" in the string. I even went as far as rewriting the entire thing in the live DB with the same results.

Mike

 

[PHV]

Replace this:
Count([Job_Status]="cmp")
with either this:
Sum(IIf([Job_Status]="cmp",1,0))
or this:
Abs(Sum([Job_Status]="cmp"))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skanrey]

Thanks PHV.

I tried both strings and they both gave the accurate calculations. I was digging through the system trying to find a relational issue causing it.

Mike