Consecutive months

[Guest]

Hi,

I have a query that counts how many incidents occour each month between a
date range. It works fine as long as there is an 'incident' each month
The problem I get is when no incidents occour in a month. I want it to count
the incidents as '0' so i can produce a report showing the averages and such
like over the year.

Here is what i have at the moment:

SELECT Year([Date of Incident])*12+DatePart('m',[Date of Incident])-1 AS
Expr1, Format$([Date of Incident],'yyyy') AS [Year], Format$([Date of
Incident],'mmmm yyyy') AS [Month], Sum([Designation]="Injury Accident -
Minor" And [Site]="Grantham")*-1 AS CountMinor, Sum([Designation]="Injury
Accident - Major" And [Site]="Grantham")*-1 AS CountMajor,
Sum([Designation]="Injury Accident - Over 3 Day" And [Site]="Grantham")*-1 AS
CountO3D, Sum([Site]<>"Grantham" And [Designation]<>"Damage Accident" And
[Designation]<>"Near Miss")*-1 AS SiteC,
[CountMinor]+[CountMajor]+[CountO3D]+[SiteC] AS Total
FROM Accident
GROUP BY Year([Date of Incident])*12+DatePart('m',[Date of Incident])-1,
Format$([Date of Incident],'yyyy'), Format$([Date of Incident],'mmmm yyyy')
HAVING (((Year([Date of Incident])*12+DatePart('m',[Date of Incident])-1)
Between
(Year([Forms]![Main]![DateFrom])*12+DatePart('m',[Forms]![Main]![DateFrom])-1)
And
(Year([Forms]![Main]![DateTo])*12+DatePart('m',[Forms]![Main]![DateTo])-1)));

Thanks for any help..

Tom

 

[Marshall Barton]

I have a query that counts how many incidents occour each month between a
date range. It works fine as long as there is an 'incident' each month
The problem I get is when no incidents occour in a month. I want it to count
the incidents as '0' so i can produce a report showing the averages and such
like over the year.

Here is what i have at the moment:

SELECT Year([Date of Incident])*12+DatePart('m',[Date of Incident])-1 AS
Expr1, Format$([Date of Incident],'yyyy') AS [Year], Format$([Date of
Incident],'mmmm yyyy') AS [Month], Sum([Designation]="Injury Accident -
Minor" And [Site]="Grantham")*-1 AS CountMinor, Sum([Designation]="Injury
Accident - Major" And [Site]="Grantham")*-1 AS CountMajor,
Sum([Designation]="Injury Accident - Over 3 Day" And [Site]="Grantham")*-1 AS
CountO3D, Sum([Site]<>"Grantham" And [Designation]<>"Damage Accident" And
[Designation]<>"Near Miss")*-1 AS SiteC,
[CountMinor]+[CountMajor]+[CountO3D]+[SiteC] AS Total
FROM Accident
GROUP BY Year([Date of Incident])*12+DatePart('m',[Date of Incident])-1,
Format$([Date of Incident],'yyyy'), Format$([Date of Incident],'mmmm yyyy')
HAVING (((Year([Date of Incident])*12+DatePart('m',[Date of Incident])-1)
Between
(Year([Forms]![Main]![DateFrom])*12+DatePart('m',[Forms]![Main]![DateFrom])-1)
And
(Year([Forms]![Main]![DateTo])*12+DatePart('m',[Forms]![Main]![DateTo])-1)));

To force all values, you should have another table with all
possible month values

 

[Guest]

It works now. I was trying to limit the number of tables and queries i have
and was hoping there was a way to do it in the query itself but i guess not.

Thanks

Tom

I have a query that counts how many incidents occour each month between a
date range. It works fine as long as there is an 'incident' each month
The problem I get is when no incidents occour in a month. I want it to count
the incidents as '0' so i can produce a report showing the averages and such
like over the year.

Here is what i have at the moment:

SELECT Year([Date of Incident])*12+DatePart('m',[Date of Incident])-1 AS
Expr1, Format$([Date of Incident],'yyyy') AS [Year], Format$([Date of
Incident],'mmmm yyyy') AS [Month], Sum([Designation]="Injury Accident -
Minor" And [Site]="Grantham")*-1 AS CountMinor, Sum([Designation]="Injury
Accident - Major" And [Site]="Grantham")*-1 AS CountMajor,
Sum([Designation]="Injury Accident - Over 3 Day" And [Site]="Grantham")*-1 AS
CountO3D, Sum([Site]<>"Grantham" And [Designation]<>"Damage Accident" And
[Designation]<>"Near Miss")*-1 AS SiteC,
[CountMinor]+[CountMajor]+[CountO3D]+[SiteC] AS Total
FROM Accident
GROUP BY Year([Date of Incident])*12+DatePart('m',[Date of Incident])-1,
Format$([Date of Incident],'yyyy'), Format$([Date of Incident],'mmmm yyyy')
HAVING (((Year([Date of Incident])*12+DatePart('m',[Date of Incident])-1)
Between
(Year([Forms]![Main]![DateFrom])*12+DatePart('m',[Forms]![Main]![DateFrom])-1)
And
(Year([Forms]![Main]![DateTo])*12+DatePart('m',[Forms]![Main]![DateTo])-1)));

To force all values, you should have another table with all
possible month values