Percentages in split/count query 1

[drewby1]

Hi

I'm trying to create a query which gives an output something like...

[tt]
ScoreDist Tot %age
1. >= 85% 303 49.11
2. 75% - 85% 187 30.31
3. 50% - 75% 110 17.83
4. < 50% 17 2.75
Total assessments 617
[/tt]

I've got the Score distribution and Total columns nailed down but what I cannot get my head around is how to calculate the percentages (%age column) within the query.

The three fields I'm using are
1. OverallScore, a number between 0 (0%) and 1 (100%)
2. AssessmentNumber, an auto-number field
3. InputDate, a date field

The query I'm currently using is...

[tt]SELECT
IIf(OverallScore>=0.85,&quot;1. >= 85%&quot;,
IIf(OverallScore>=0.75,&quot;2. 75% - 85%&quot;,
IIf(OverallScore>=0.50,&quot;3. 50% - 75%&quot;,
&quot;4. < 50%&quot)) AS ScoreDist,
Count(AssessmentNumber) AS Tot

FROM qryAssess_ALL_LinkedData
WHERE (InputDate BETWEEN Forms.frmReports.calFrom.Value And Forms.frmReports.calTo.Value)

GROUP BY
IIf(OverallScore>=0.85,&quot;1. >= 85%&quot;,
IIf(OverallScore>=0.75,&quot;2. 75% - 85%&quot;,
IIf(OverallScore>=0.50,&quot;3. 50% - 75%&quot;,
&quot;4. < 50%&quot))

UNION SELECT &quot;Total assessments&quot;, Count(*)
FROM qryAssess_ALL_LinkedData
WHERE (InputDate BETWEEN Forms.frmReports.calFrom.Value And Forms.frmReports.calTo.Value);[/tt]

Any help or pointers would be greatly appreciated. I'm thinking that maybe this will have to done with a bit of VB code within a module and using a temporary table. I'm hoping that someone out there can help me out with a nice elegant solution using a query or two.

Good luck people and thanks in advance!

Andy

 

[Norris68]

SELECT
IIf(OverallScore>=0.85,&quot;1. >= 85%&quot;,
IIf(OverallScore>=0.75,&quot;2. 75% - 85%&quot;,
IIf(OverallScore>=0.50,&quot;3. 50% - 75%&quot;,
&quot;4. < 50%&quot)) AS ScoreDist,
Count(AssessmentNumber) AS Tot,
Count(AssessmentNumber)/DCount(&quot;*&quot;,&quot;qryAssess_ALL_LinkedData&quot;,&quot;InputDate BETWEEN Forms.frmReports.calFrom.Value And Forms.frmReports.calTo.Value&quot) AS Percentage

FROM qryAssess_ALL_LinkedData
WHERE (InputDate BETWEEN Forms.frmReports.calFrom.Value And Forms.frmReports.calTo.Value)

GROUP BY
IIf(OverallScore>=0.85,&quot;1. >= 85%&quot;,
IIf(OverallScore>=0.75,&quot;2. 75% - 85%&quot;,
IIf(OverallScore>=0.50,&quot;3. 50% - 75%&quot;,
&quot;4. < 50%&quot))

UNION SELECT &quot;Total assessments&quot;, Count(*), 1
FROM qryAssess_ALL_LinkedData
WHERE (InputDate BETWEEN Forms.frmReports.calFrom.Value And Forms.frmReports.calTo.Value);

 

[drewby1]

Shellin' peas!

Norris68, you're a star and thanks very much for a quick and easy to use response (CTRL+C, ALT+TAB, CTRL+V). Works like a charm...

Andy