Counting by range, and grouping by month

[APHNB]

Hi everyone!

I have a database that tracks the risk scores (NBScore) of projects being introduced into the system. The risk score is on a scale from 1-9. What I need to do is generate a report that shows the number of low risk(1-3), medium risk (4-6), high risk (7-9), and unknown score (NULL) projects per month (based on ScopeReviewDate) - I'm running into trouble with this.

I have four separate queries that pull out the number of low, medium, high, and unknown per month, but I'm not sure how to have them all appear on one report. The queries all follow the same structure, which is as follows

SELECT Month(ProjEvalTrackingScoreCheck.ScopeReviewDate) AS ['Month'], Count(ProjEvalTrackingScoreCheck.NBScore) AS CountOfNBScore
FROM ProjEvalTrackingScoreCheck
WHERE (((Year([ProjEvalTrackingScoreCheck.ScopeReviewDate]))=2011) AND ((ProjEvalTrackingScoreCheck.[NBScore]) Between 1 And 3))
GROUP BY Month(ScopeReviewDate);

I am not sure how to combine these queries onto one report.

Thanks for any help!

Alex

 

[vbaInet]

You should have a table as follows:

RiskID | RiskType
=================
1        Low Risk
2        Low Risk
3        Low Risk
4        Medium Risk
5        Medium Risk
6        Medium Risk
7        High Risk
8        High Risk
9        High Risk

This table willl only be used for displaying the categorised categories and you can do a LEFT JOIN or RIGHT JOIN to the table in order to get the uncategorised records displayed.

Instead of four queries, you can use just one query to pull the data for the three risk types plus the uncategorised types. Bring in the new table into the query and as mentioned above, change the join so that you get all records from the parent side. The query (in Query Design) will now look like this:

NBScore        [COLOR=Red]RiskType[/COLOR]        ProjMonth: Month(ScopeReviewDate)      NBScore         ProjYear: Year(ScopeReviewDate) 
TableName      [COLOR=Red]NewTableName[/COLOR]                                           TableName
Group By       Group By        Group By                               Count           Group By
                                                                                      2011

Notice I've called the Month field ProjMonth because you should avoid using names that are Access reserved keywords.

And welcome to the forum!

 

[APHNB]

Thanks for the response!

The problem I'm having is that I'm getting results like this:

projMonth | RiskType | ScoreCount

1 | Low | 2
2 | Low | 5
1 | Medium | 4
2 | Medium | 2
3 | Medium | 5
1 | High | 12
2 | High | 5
3 | High | 1
12 | High | 1
1 | Unknown | 4
2 | Unknown | 1
3 | Unknown | 5
4 | Unknown | 4

What I would like to eventually accomplish is to have it look something like this (in a chart or a datasheet)

projMonth | L | M | H | U
Jan | 2 | 4 | 12 | 4
Feb
March

etc. and I don't really know how to get to that point. It may very well not be something that's possible through queries, although I certainly hope it is.

Your response brought me closer to getting it though!

Alex

 

[philben]

Hi,


Two solutions out of others.


First : Pivot query

TRANSFORM Count(*) AS ProjRisk
SELECT MonthName(Month([ScopeReviewDate]),True) AS ProjMonth
FROM ProjEvalTrackingScoreCheck AS T
WHERE Year([ScopeReviewDate])=2011
GROUP BY MonthName(Month([ScopeReviewDate]),True), Month([ScopeReviewDate])
ORDER BY Month([ScopeReviewDate])
PIVOT Choose((Nz([NBScore],10)-1)\3+1,"L","M","H","U") In ("L","M","H","U");

Second solution :

SELECT 
    MonthName(Month([ScopeReviewDate]),True) AS ProjMonth, 
    Count(IIf([NBScore] Between 1 And 3,1,Null)) AS L, 
    Count(IIf([NBScore] Between 4 And 6,1,Null)) AS M, 
    Count(IIf([NBScore] Between 7 And 9,1,Null)) AS H, 
    Count(IIf([NBScore] Is Null,1,Null)) AS U
FROM ProjEvalTrackingScoreCheck AS T
WHERE Year([ScopeReviewDate])=2011
GROUP BY MonthName(Month([ScopeReviewDate]),True), Month([ScopeReviewDate])
ORDER BY Month([ScopeReviewDate]);

Philippe