Split By Month

[Guest]

The following code produces a count Per Reviewer and Per Month.
I'm trying to split out each month in a seperate column, this is not
working, could you please help me out??

Trying to do something like this:
Jan: IIf([Months]= "Jan")

This is my Query:
SELECT Count(q_QualityKeyIndicators.ICNNo) AS CountOfICNNo,
q_QualityKeyIndicators.Reviewer, IIf([IssueCloseDate] Is
Null,Null,MonthName(Month([IssueCloseDate]),True)) AS Months
FROM q_QualityKeyIndicators
GROUP BY q_QualityKeyIndicators.Reviewer, IIf([IssueCloseDate] Is
Null,Null,MonthName(Month([IssueCloseDate]),True));

 

[John Spencer]

Take a look at Crosstab queries
Row: Reviewer
ColumnHeader: MonthName
Value: Count ICNo


Otherwise, you can use one of the following methods to get the information
Count(IIF(Month(IssueCloseDate)=1, 1, Null)) as JanuaryCount
or
Abs(Sum(Month(IssueCloseDate)=1)) as JanuaryCount

 

[Guest]

Ok, based on what you wrote I have added seperate counts for each month.
This is what I have for Jan & Feb...

Data looks fine. Thanks


SELECT Count(IIF(Month(IssueCloseDate)=1, 1, Null)) as
JanCount,Count(IIF(Month(IssueCloseDate)=2, 1, Null)) as FebCount,
Count(q_QualityKeyIndicators.ICNNo) AS CountOfICNNo,
q_QualityKeyIndicators.Reviewer, IIf([IssueCloseDate] Is
Null,Null,MonthName(Month([IssueCloseDate]),True)) AS Months
FROM q_QualityKeyIndicators
GROUP BY q_QualityKeyIndicators.Reviewer, IIf([IssueCloseDate] Is
Null,Null,MonthName(Month([IssueCloseDate]),True));

Take a look at Crosstab queries
Row: Reviewer
ColumnHeader: MonthName
Value: Count ICNo


Otherwise, you can use one of the following methods to get the information
Count(IIF(Month(IssueCloseDate)=1, 1, Null)) as JanuaryCount
or
Abs(Sum(Month(IssueCloseDate)=1)) as JanuaryCount

The following code produces a count Per Reviewer and Per Month.
I'm trying to split out each month in a seperate column, this is not
working, could you please help me out??

Trying to do something like this:
Jan: IIf([Months]= "Jan")

This is my Query:
SELECT Count(q_QualityKeyIndicators.ICNNo) AS CountOfICNNo,
q_QualityKeyIndicators.Reviewer, IIf([IssueCloseDate] Is
Null,Null,MonthName(Month([IssueCloseDate]),True)) AS Months
FROM q_QualityKeyIndicators
GROUP BY q_QualityKeyIndicators.Reviewer, IIf([IssueCloseDate] Is
Null,Null,MonthName(Month([IssueCloseDate]),True));