Count of Multiple Ranges in Single Query

[Elysynn]

Hello,

I have a list of employees with scores from which I need to count how many are >94.99, between 90 and 94.99 and <90. I was wondering if there was a way to do this without having to do three separate queries. Below is the SQL I have so far...

SELECT qryAgentEvals3.Site, qryTotalEvalCount.CountOfSite AS AgentCount, Count(qryAgentEvals3.Score) AS ExceedCt, [ExceedCt]/[AgentCount] AS ExceedPct
FROM qryAgentEvals3 INNER JOIN qryTotalEvalCount ON qryAgentEvals3.Site = qryTotalEvalCount.Site
WHERE (((qryAgentEvals3.Score)>94.99))
GROUP BY qryAgentEvals3.Site, qryTotalEvalCount.CountOfSite, [ExceedCt]/[AgentCount];

Any insight you may have is very appreciated.

TIA,
Elysynn

 

[Remou]

Have you looked at a crosstab query?

 

[Elysynn]

I've worked some with crosstabs, but I'm not sure how I would set it up in this case.

 

[Remou]

Sorry, I did not read your post properly. I cannot seem to get your SQL to work.

 

[Golom]

SELECT A.Site, T.CountOfSite AS AgentCount, 
       IIF(A.Score<90, "<90", 
       IIF(A.Score<=94.99, "90-94.99", ">94.99")) As [Score Group]
       Count(A.Score) AS ExceedCt, [ExceedCt]/[AgentCount] AS ExceedPct

FROM qryAgentEvals3 A INNER JOIN qryTotalEvalCount T ON A.Site = T.Site

GROUP BY A.Site, T.CountOfSite, [ExceedCt]/[AgentCount],
         IIF(A.Score<90, "<90", 
         IIF(A.Score<=94.99, "90-94.99", ">94.99"))

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[dhookom]

I would create a table of score ranges so that the min and max scores are in data, not maintained in complex expressions.
[tt][blue]
tblScoreRanges
==================
ScoreMin
ScoreMax
ScoreTitle
[/blue][/tt]
This would allow you to create a single totals query or crosstab quite easily.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Elysynn]

Thank you for your suggestions - I am going to have to try them out. Unfortunately - this has been pushed to the back burner for more pressing projects...

-Elysynn