getting precentages from table

[Lanser]

pcnt: Sum(IIf([GradeID]>=3,1,0))/Count(*)*100
I'm tryting to use this expression to give a precentage of GradeId above 3 for each employee ie Janet has 2 out of 11 to give a percentage of 18% howeve the above gives Janet 100%

sql from the query below

SELECT tblHandswabs.Surname, tblHandswabs.First_Name, tblHandswabs.ShiftID, tblHandswabs.Date, Count(tblHandswabs.HandswabsID) AS CountOfHandswabsID
FROM tblHandswabs
GROUP BY tblHandswabs.Surname, tblHandswabs.First_Name, tblHandswabs.ShiftID, tblHandswabs.Date
HAVING (((Sum(IIf([GradeID]>=3,1,0))/Count(*)*100)>=5) AND ((tblHandswabs.Date)>#1/1/2010#))
ORDER BY Sum(IIf([GradeID]>=3,1,0))/Count(*)*100 DESC;

 

[jzwp22]

Where is gradeID coming from? I'll assume that it is coming from tblHandswabs

I am thinking that you might have to have separate queries. One that does the evaluation of the grade, then another that does the counting & summing and then one last one to determine the percentage.



query name: qryGetGrades
SELECT tblHandswabs.HandswabsID, tblHandswabs.Surname, tblHandswabs.First_Name, tblHandswabs.ShiftID, tblHandswabs.Date, IIf([GradeID]>=3,1,0) as GradeEval
FROM tblHandswabs
WHERE tblHandswabs.Date)>#1/1/2010#

query name: qrySumGrades
SELECT qryGetGrades.Surname, qryGetGrades.First_Name, qryGetGrades.ShiftID, qryGetGrades.Date, SUM(qryGetGrades.GradeEval) as SumOfGrades, Count(qryGetGrades.HandswabsID) as TotalCounted
FROM qryGetGrades
GROUP BY qryGetGrades.Surname, qryGetGrades.First_Name, qryGetGrades.ShiftID, qryGetGrades.Date

query name: qryGetPercentage
SELECT qrySumGrades.Surname, qrySumGrades.First_Name, qrySumGrades.ShiftID, qrySumGrades.Date, qrySumGrades.SumOfGrades/qrySumGrades.TotalCounted *100 as GradePercentage
FROM qrySumGrades

 

[Lanser]

Thanks for the help

I had to remove the date fields otherwise it was treating each date as a unique record but works fine now

 

[vbaInet]

So now Janet isn't getting 100%? That's sad for Janet

Yes it does get a bit tricky when these fields are left in.

 

[Lanser]

hehe actually thats good for Janet 100% would mean she's not washing her hands at all, a problem in a food factory

 

[vbaInet]

Ahhh.... well Janet would be very pleased