Count Per Month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to count how many Cases per month per Reviewer.

My Query has a value called months:
Months: IIf([IssueCloseDate] Is
Null,Null,MonthName(Month([IssueCloseDate]),True))

In my report I want to show a count ie: Jan=2, Feb=19, Mar=7.....

I Sorted/Grouped the report by Reviewer.

I'm trying to put one text box for Jan, another for Feb, etc...

This code is not giving me a total - what am I doing wrong??????

=Sum(IIf([Months]=Jan))
or
=Sum(IIf([Months])="Jan"))

Thanks
 
Use a totals query:

Format([IssueCloseDate],"mmm") - Group By

[Issue] - Count

[Reviewer] - Group By
 
Everything in the below code works EXCEPT the last line - where I'm adding a
date range. I have to allow the use to enter a date range.

Since the code uses MONTH to calculate the count, how can I allo the user to
enter ie: 01/01/2006 - 5/5/2006????????

SELECT Count(tblQualityData.ICNNo) AS CountOfICNNo, tblQualityData.Reviewer,
IIf([IssueCloseDate] Is Null,Null,MonthName(Month([IssueCloseDate]),True)) AS
Months, tblQualityData.IssueCloseDate
FROM tblQualityData
GROUP BY tblQualityData.Reviewer, IIf([IssueCloseDate] Is
Null,Null,MonthName(Month([IssueCloseDate]),True)),
tblQualityData.IssueCloseDate
HAVING (((tblQualityData.IssueCloseDate) Between
[forms]![f_KeyIndicators].[txtstart] And [forms]![f_KeyIndicators].[txtend]));





Jeff C said:
Use a totals query:

Format([IssueCloseDate],"mmm") - Group By

[Issue] - Count

[Reviewer] - Group By
--
Jeff C
Live Well .. Be Happy In All You Do


Dan @BCBS said:
I'm trying to count how many Cases per month per Reviewer.

My Query has a value called months:
Months: IIf([IssueCloseDate] Is
Null,Null,MonthName(Month([IssueCloseDate]),True))

In my report I want to show a count ie: Jan=2, Feb=19, Mar=7.....

I Sorted/Grouped the report by Reviewer.

I'm trying to put one text box for Jan, another for Feb, etc...

This code is not giving me a total - what am I doing wrong??????

=Sum(IIf([Months]=Jan))
or
=Sum(IIf([Months])="Jan"))

Thanks
Click to expand...
Click to expand...
 
Build your date range criteria into a source query for your totals query
--
Jeff C
Live Well .. Be Happy In All You Do


Dan @BCBS said:
Everything in the below code works EXCEPT the last line - where I'm adding a
date range. I have to allow the use to enter a date range.

Since the code uses MONTH to calculate the count, how can I allo the user to
enter ie: 01/01/2006 - 5/5/2006????????

SELECT Count(tblQualityData.ICNNo) AS CountOfICNNo, tblQualityData.Reviewer,
IIf([IssueCloseDate] Is Null,Null,MonthName(Month([IssueCloseDate]),True)) AS
Months, tblQualityData.IssueCloseDate
FROM tblQualityData
GROUP BY tblQualityData.Reviewer, IIf([IssueCloseDate] Is
Null,Null,MonthName(Month([IssueCloseDate]),True)),
tblQualityData.IssueCloseDate
HAVING (((tblQualityData.IssueCloseDate) Between
[forms]![f_KeyIndicators].[txtstart] And [forms]![f_KeyIndicators].[txtend]));





Jeff C said:
Use a totals query:

Format([IssueCloseDate],"mmm") - Group By

[Issue] - Count

[Reviewer] - Group By
--
Jeff C
Live Well .. Be Happy In All You Do


Dan @BCBS said:
I'm trying to count how many Cases per month per Reviewer.

My Query has a value called months:
Months: IIf([IssueCloseDate] Is
Null,Null,MonthName(Month([IssueCloseDate]),True))

In my report I want to show a count ie: Jan=2, Feb=19, Mar=7.....

I Sorted/Grouped the report by Reviewer.

I'm trying to put one text box for Jan, another for Feb, etc...

This code is not giving me a total - what am I doing wrong??????

=Sum(IIf([Months]=Jan))
or
=Sum(IIf([Months])="Jan"))

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top