T
TomK
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
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