6 Month Rolling Average in Query

C

Chris

I need some help writing this query. This should be simple, yet I can't get
it to work as I want. I have a simple database to keep track of incidents.
I need a count of the number of incidents each month (which I have) and for
each month I need the average number of incidents for the previous 6 months
(this is where I need help).

Ex data:

IncidentMonth CountofIncidents 6MoAvgIncidents
Oct-02 16 -
Nov-02 12 -
Dec-02 15 -
Jan-03 21 -
Feb-03 7 -
Mar-03 12 13.83333333
Apr-03 10 12.83333333
May-03 15 13.33333333
Jun-03 18 13.83333333
Jul-03 25 14.5
Aug-03 16 16
Sep-03 16 16.66666667
Oct-03 19 18.16666667
Nov-03 10 17.33333333
Dec-03 12 16.33333333


Here is my SQL:

SELECT Format([IncidentDate],"yyyy") AS SortbyYear,
Format([IncidentDate],"mm") AS SortbyMonth, Format([IncidentDate],"mmm-yy")
AS IncidentMonth, Count(Incidents.Category) AS CountOfIncidents, "HelpHere"
AS 6MoAvgIncidents
FROM Incidents
GROUP BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy"), "HelpHere"
ORDER BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy");


Thanks in advance for any help offered!
 
K

KARL DEWEY

Is this what you are looking for?
SELECT [Enter ending month (6/1/2006)] AS [Report Ending Month],
Count([Category])/6 AS [6 Months Average]
FROM Incidents
WHERE (((Incidents.IncidentDate) Between DateAdd("m",-6,[Enter ending month
(6/1/2006)]) And [Enter ending month (6/1/2006)]))
GROUP BY [Enter ending month (6/1/2006)];
 
C

Chris

I worked with this for a while, but can't get what I need out of it. This
does calculate the 6mo average for the one particular ending month you enter.
However, I'd like to see the query in Design view looking similar to this,
with a column showing the 6 month average that corresponds to each month.
I've a feeling this is a combination of subqueries, which I can't figure out.
IncidentMonth CountofIncidents 6MoAvgIncidents
Mar-03 12 13.83333333
Apr-03 10 12.83333333
May-03 15 13.33333333
Jun-03 18 13.83333333
Jul-03 25 14.5
Aug-03 16 16
Sep-03 16 16.66666667
Oct-03 19 18.16666667
Nov-03 10 17.33333333
Dec-03 12 16.33333333
Click to expand...


KARL DEWEY said:
Is this what you are looking for?
SELECT [Enter ending month (6/1/2006)] AS [Report Ending Month],
Count([Category])/6 AS [6 Months Average]
FROM Incidents
WHERE (((Incidents.IncidentDate) Between DateAdd("m",-6,[Enter ending month
(6/1/2006)]) And [Enter ending month (6/1/2006)]))
GROUP BY [Enter ending month (6/1/2006)];

--
KARL DEWEY
Build a little - Test a little


Chris said:
I need some help writing this query. This should be simple, yet I can't get
it to work as I want. I have a simple database to keep track of incidents.
I need a count of the number of incidents each month (which I have) and for
each month I need the average number of incidents for the previous 6 months
(this is where I need help).

Ex data:

IncidentMonth CountofIncidents 6MoAvgIncidents
Oct-02 16 -
Nov-02 12 -
Dec-02 15 -
Jan-03 21 -
Feb-03 7 -
Mar-03 12 13.83333333
Apr-03 10 12.83333333
May-03 15 13.33333333
Jun-03 18 13.83333333
Jul-03 25 14.5
Aug-03 16 16
Sep-03 16 16.66666667
Oct-03 19 18.16666667
Nov-03 10 17.33333333
Dec-03 12 16.33333333


Here is my SQL:

SELECT Format([IncidentDate],"yyyy") AS SortbyYear,
Format([IncidentDate],"mm") AS SortbyMonth, Format([IncidentDate],"mmm-yy")
AS IncidentMonth, Count(Incidents.Category) AS CountOfIncidents, "HelpHere"
AS 6MoAvgIncidents
FROM Incidents
GROUP BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy"), "HelpHere"
ORDER BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy");


Thanks in advance for any help offered!
Click to expand...
Click to expand...
 
C

Chris

I'll explain a little more what I'm doing here in case I'm missing an easier
way to do this.

This data will be for a chart report--a simple line graph plotting the
number of incidents for each month in a given year (selected from a drop-down
box on a form).

Now I also want to add a trendline showing the 6 month rolling average.
Using the trendling in the chart creator, the line doesn't start until the
6th month on the graph. The first 6 months would draw data from the previous
year.

I thought if I could build a query that matched up the 6month average to
each particular month, then that column of data would create the 2nd line on
the graph.

Otherwise, how can I get the chart to show the line graph's trendline to
extend from month 1-6?



KARL DEWEY said:
Is this what you are looking for?
SELECT [Enter ending month (6/1/2006)] AS [Report Ending Month],
Count([Category])/6 AS [6 Months Average]
FROM Incidents
WHERE (((Incidents.IncidentDate) Between DateAdd("m",-6,[Enter ending month
(6/1/2006)]) And [Enter ending month (6/1/2006)]))
GROUP BY [Enter ending month (6/1/2006)];

--
KARL DEWEY
Build a little - Test a little


Chris said:
I need some help writing this query. This should be simple, yet I can't get
it to work as I want. I have a simple database to keep track of incidents.
I need a count of the number of incidents each month (which I have) and for
each month I need the average number of incidents for the previous 6 months
(this is where I need help).

Ex data:

IncidentMonth CountofIncidents 6MoAvgIncidents
Oct-02 16 -
Nov-02 12 -
Dec-02 15 -
Jan-03 21 -
Feb-03 7 -
Mar-03 12 13.83333333
Apr-03 10 12.83333333
May-03 15 13.33333333
Jun-03 18 13.83333333
Jul-03 25 14.5
Aug-03 16 16
Sep-03 16 16.66666667
Oct-03 19 18.16666667
Nov-03 10 17.33333333
Dec-03 12 16.33333333


Here is my SQL:

SELECT Format([IncidentDate],"yyyy") AS SortbyYear,
Format([IncidentDate],"mm") AS SortbyMonth, Format([IncidentDate],"mmm-yy")
AS IncidentMonth, Count(Incidents.Category) AS CountOfIncidents, "HelpHere"
AS 6MoAvgIncidents
FROM Incidents
GROUP BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy"), "HelpHere"
ORDER BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy");


Thanks in advance for any help offered!
Click to expand...
Click to expand...
 
K

KARL DEWEY

I think I did it in two queries as I do not know subqueries.
Chris_Running_Mon ---
SELECT Format([IncidentDate],"mm/\1/yyyy") AS Incident_Month,
Count(Incidents.Category) AS CountOfCategory
FROM Incidents
GROUP BY Format([IncidentDate],"mm/\1/yyyy");


SELECT DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) & " - "
& CVDate([Chris_Running_Mon].[Incident_Month]) AS [Month-Year],
Sum([Chris_Running_Mon_1].[CountOfCategory])/6 AS [Running 6 Months Average]
FROM Chris_Running_Mon, Chris_Running_Mon AS Chris_Running_Mon_1
WHERE (((CVDate([Chris_Running_Mon_1].[Incident_Month])) Between
CVDate([Chris_Running_Mon].[Incident_Month]) And
DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month]))))
GROUP BY CVDate([Chris_Running_Mon].[Incident_Month]),
DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) & " - " &
CVDate([Chris_Running_Mon].[Incident_Month]);

--
KARL DEWEY
Build a little - Test a little


Chris said:
I'll explain a little more what I'm doing here in case I'm missing an easier
way to do this.

This data will be for a chart report--a simple line graph plotting the
number of incidents for each month in a given year (selected from a drop-down
box on a form).

Now I also want to add a trendline showing the 6 month rolling average.
Using the trendling in the chart creator, the line doesn't start until the
6th month on the graph. The first 6 months would draw data from the previous
year.

I thought if I could build a query that matched up the 6month average to
each particular month, then that column of data would create the 2nd line on
the graph.

Otherwise, how can I get the chart to show the line graph's trendline to
extend from month 1-6?



KARL DEWEY said:
Is this what you are looking for?
SELECT [Enter ending month (6/1/2006)] AS [Report Ending Month],
Count([Category])/6 AS [6 Months Average]
FROM Incidents
WHERE (((Incidents.IncidentDate) Between DateAdd("m",-6,[Enter ending month
(6/1/2006)]) And [Enter ending month (6/1/2006)]))
GROUP BY [Enter ending month (6/1/2006)];

--
KARL DEWEY
Build a little - Test a little


Chris said:
I need some help writing this query. This should be simple, yet I can't get
it to work as I want. I have a simple database to keep track of incidents.
I need a count of the number of incidents each month (which I have) and for
each month I need the average number of incidents for the previous 6 months
(this is where I need help).

Ex data:

IncidentMonth CountofIncidents 6MoAvgIncidents
Oct-02 16 -
Nov-02 12 -
Dec-02 15 -
Jan-03 21 -
Feb-03 7 -
Mar-03 12 13.83333333
Apr-03 10 12.83333333
May-03 15 13.33333333
Jun-03 18 13.83333333
Jul-03 25 14.5
Aug-03 16 16
Sep-03 16 16.66666667
Oct-03 19 18.16666667
Nov-03 10 17.33333333
Dec-03 12 16.33333333


Here is my SQL:

SELECT Format([IncidentDate],"yyyy") AS SortbyYear,
Format([IncidentDate],"mm") AS SortbyMonth, Format([IncidentDate],"mmm-yy")
AS IncidentMonth, Count(Incidents.Category) AS CountOfIncidents, "HelpHere"
AS 6MoAvgIncidents
FROM Incidents
GROUP BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy"), "HelpHere"
ORDER BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy");


Thanks in advance for any help offered!
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

A little change ---
SELECT DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) & " - "
& DateAdd("m",1,CVDate([Chris_Running_Mon].[Incident_Month]))-1 AS
[Month-Year],

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
I think I did it in two queries as I do not know subqueries.
Chris_Running_Mon ---
SELECT Format([IncidentDate],"mm/\1/yyyy") AS Incident_Month,
Count(Incidents.Category) AS CountOfCategory
FROM Incidents
GROUP BY Format([IncidentDate],"mm/\1/yyyy");


SELECT DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) & " - "
& CVDate([Chris_Running_Mon].[Incident_Month]) AS [Month-Year],
Sum([Chris_Running_Mon_1].[CountOfCategory])/6 AS [Running 6 Months Average]
FROM Chris_Running_Mon, Chris_Running_Mon AS Chris_Running_Mon_1
WHERE (((CVDate([Chris_Running_Mon_1].[Incident_Month])) Between
CVDate([Chris_Running_Mon].[Incident_Month]) And
DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month]))))
GROUP BY CVDate([Chris_Running_Mon].[Incident_Month]),
DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) & " - " &
CVDate([Chris_Running_Mon].[Incident_Month]);

--
KARL DEWEY
Build a little - Test a little


Chris said:
I'll explain a little more what I'm doing here in case I'm missing an easier
way to do this.

This data will be for a chart report--a simple line graph plotting the
number of incidents for each month in a given year (selected from a drop-down
box on a form).

Now I also want to add a trendline showing the 6 month rolling average.
Using the trendling in the chart creator, the line doesn't start until the
6th month on the graph. The first 6 months would draw data from the previous
year.

I thought if I could build a query that matched up the 6month average to
each particular month, then that column of data would create the 2nd line on
the graph.

Otherwise, how can I get the chart to show the line graph's trendline to
extend from month 1-6?



KARL DEWEY said:
Is this what you are looking for?
SELECT [Enter ending month (6/1/2006)] AS [Report Ending Month],
Count([Category])/6 AS [6 Months Average]
FROM Incidents
WHERE (((Incidents.IncidentDate) Between DateAdd("m",-6,[Enter ending month
(6/1/2006)]) And [Enter ending month (6/1/2006)]))
GROUP BY [Enter ending month (6/1/2006)];

--
KARL DEWEY
Build a little - Test a little


:

I need some help writing this query. This should be simple, yet I can't get
it to work as I want. I have a simple database to keep track of incidents.
I need a count of the number of incidents each month (which I have) and for
each month I need the average number of incidents for the previous 6 months
(this is where I need help).

Ex data:

IncidentMonth CountofIncidents 6MoAvgIncidents
Oct-02 16 -
Nov-02 12 -
Dec-02 15 -
Jan-03 21 -
Feb-03 7 -
Mar-03 12 13.83333333
Apr-03 10 12.83333333
May-03 15 13.33333333
Jun-03 18 13.83333333
Jul-03 25 14.5
Aug-03 16 16
Sep-03 16 16.66666667
Oct-03 19 18.16666667
Nov-03 10 17.33333333
Dec-03 12 16.33333333


Here is my SQL:

SELECT Format([IncidentDate],"yyyy") AS SortbyYear,
Format([IncidentDate],"mm") AS SortbyMonth, Format([IncidentDate],"mmm-yy")
AS IncidentMonth, Count(Incidents.Category) AS CountOfIncidents, "HelpHere"
AS 6MoAvgIncidents
FROM Incidents
GROUP BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy"), "HelpHere"
ORDER BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy");


Thanks in advance for any help offered!
Click to expand...
Click to expand...
Click to expand...
 
C

Chris

Thank you very much, this is what I was looking for. With a little
modification, it works as intended with my line graph. Thanks again.

KARL DEWEY said:
A little change ---
SELECT DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) & " - "
& DateAdd("m",1,CVDate([Chris_Running_Mon].[Incident_Month]))-1 AS
[Month-Year],

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
I think I did it in two queries as I do not know subqueries.
Chris_Running_Mon ---
SELECT Format([IncidentDate],"mm/\1/yyyy") AS Incident_Month,
Count(Incidents.Category) AS CountOfCategory
FROM Incidents
GROUP BY Format([IncidentDate],"mm/\1/yyyy");


SELECT DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) & " - "
& CVDate([Chris_Running_Mon].[Incident_Month]) AS [Month-Year],
Sum([Chris_Running_Mon_1].[CountOfCategory])/6 AS [Running 6 Months Average]
FROM Chris_Running_Mon, Chris_Running_Mon AS Chris_Running_Mon_1
WHERE (((CVDate([Chris_Running_Mon_1].[Incident_Month])) Between
CVDate([Chris_Running_Mon].[Incident_Month]) And
DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month]))))
GROUP BY CVDate([Chris_Running_Mon].[Incident_Month]),
DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) & " - " &
CVDate([Chris_Running_Mon].[Incident_Month]);

--
KARL DEWEY
Build a little - Test a little


Chris said:
I'll explain a little more what I'm doing here in case I'm missing an easier
way to do this.

This data will be for a chart report--a simple line graph plotting the
number of incidents for each month in a given year (selected from a drop-down
box on a form).

Now I also want to add a trendline showing the 6 month rolling average.
Using the trendling in the chart creator, the line doesn't start until the
6th month on the graph. The first 6 months would draw data from the previous
year.

I thought if I could build a query that matched up the 6month average to
each particular month, then that column of data would create the 2nd line on
the graph.

Otherwise, how can I get the chart to show the line graph's trendline to
extend from month 1-6?



:

Is this what you are looking for?
SELECT [Enter ending month (6/1/2006)] AS [Report Ending Month],
Count([Category])/6 AS [6 Months Average]
FROM Incidents
WHERE (((Incidents.IncidentDate) Between DateAdd("m",-6,[Enter ending month
(6/1/2006)]) And [Enter ending month (6/1/2006)]))
GROUP BY [Enter ending month (6/1/2006)];

--
KARL DEWEY
Build a little - Test a little


:

I need some help writing this query. This should be simple, yet I can't get
it to work as I want. I have a simple database to keep track of incidents.
I need a count of the number of incidents each month (which I have) and for
each month I need the average number of incidents for the previous 6 months
(this is where I need help).

Ex data:

IncidentMonth CountofIncidents 6MoAvgIncidents
Oct-02 16 -
Nov-02 12 -
Dec-02 15 -
Jan-03 21 -
Feb-03 7 -
Mar-03 12 13.83333333
Apr-03 10 12.83333333
May-03 15 13.33333333
Jun-03 18 13.83333333
Jul-03 25 14.5
Aug-03 16 16
Sep-03 16 16.66666667
Oct-03 19 18.16666667
Nov-03 10 17.33333333
Dec-03 12 16.33333333


Here is my SQL:

SELECT Format([IncidentDate],"yyyy") AS SortbyYear,
Format([IncidentDate],"mm") AS SortbyMonth, Format([IncidentDate],"mmm-yy")
AS IncidentMonth, Count(Incidents.Category) AS CountOfIncidents, "HelpHere"
AS 6MoAvgIncidents
FROM Incidents
GROUP BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy"), "HelpHere"
ORDER BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy");


Thanks in advance for any help offered!
Click to expand...
Click to expand...
Click to expand...
 
N

Natalia

Hello,

I'm trying to use this solution to create a 12-month rolling average. The
math I need to do in the end is more complicated than a linear average; I
need to weight the numbers using values in another column. But that said, I
tried using the code to calculate a linear average but the results I get
don't make sense.

This is the query I'm using:

SELECT DateAdd("m",-11,CVDate([EHSIRCalculationSmart].[YrMo])) & " - " &
DateAdd("m",1,CVDate([EHSIRCalculationSmart].[YrMo]))-1 AS [Month-Year],
Sum([EHSIRCalculationSmart1].[SumOfTotalPointsForEHSIR])/12 AS [Rolling 12
Months Average]
FROM EHSIRCalculationSmart, EHSIRCalculationSmart AS EHSIRCalculationSmart1
WHERE (((CVDate([EHSIRCalculationSmart1].[YrMo])) Between
CVDate([EHSIRCalculationSmart].[YrMo]) And
DateAdd("m",-11,CVDate([EHSIRCalculationSmart].[YrMo]))))
GROUP BY CVDate([EHSIRCalculationSmart].[YrMo]),
DateAdd("m",-11,CVDate([EHSIRCalculationSmart].[YrMo])) & " - " &
CVDate([EHSIRCalculationSmart].[YrMo]);

The data I have, for example, sums up 20 incidents in January of 2006, and
17 in February of 2006, so I'd expect the first row to say 20/12 = 1.666;
instead it reads 35. Would this have something to do with the fact that the
query I'm based on is itself a "totals" query?

Thanks in advance for the help!

Me :)

Chris said:
Thank you very much, this is what I was looking for. With a little
modification, it works as intended with my line graph. Thanks again.

KARL DEWEY said:
A little change ---
SELECT DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) & " - "
& DateAdd("m",1,CVDate([Chris_Running_Mon].[Incident_Month]))-1 AS
[Month-Year],

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
I think I did it in two queries as I do not know subqueries.
Chris_Running_Mon ---
SELECT Format([IncidentDate],"mm/\1/yyyy") AS Incident_Month,
Count(Incidents.Category) AS CountOfCategory
FROM Incidents
GROUP BY Format([IncidentDate],"mm/\1/yyyy");


SELECT DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) & " - "
& CVDate([Chris_Running_Mon].[Incident_Month]) AS [Month-Year],
Sum([Chris_Running_Mon_1].[CountOfCategory])/6 AS [Running 6 Months Average]
FROM Chris_Running_Mon, Chris_Running_Mon AS Chris_Running_Mon_1
WHERE (((CVDate([Chris_Running_Mon_1].[Incident_Month])) Between
CVDate([Chris_Running_Mon].[Incident_Month]) And
DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month]))))
GROUP BY CVDate([Chris_Running_Mon].[Incident_Month]),
DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) & " - " &
CVDate([Chris_Running_Mon].[Incident_Month]);

--
KARL DEWEY
Build a little - Test a little


:

I'll explain a little more what I'm doing here in case I'm missing an easier
way to do this.

This data will be for a chart report--a simple line graph plotting the
number of incidents for each month in a given year (selected from a drop-down
box on a form).

Now I also want to add a trendline showing the 6 month rolling average.
Using the trendling in the chart creator, the line doesn't start until the
6th month on the graph. The first 6 months would draw data from the previous
year.

I thought if I could build a query that matched up the 6month average to
each particular month, then that column of data would create the 2nd line on
the graph.

Otherwise, how can I get the chart to show the line graph's trendline to
extend from month 1-6?



:

Is this what you are looking for?
SELECT [Enter ending month (6/1/2006)] AS [Report Ending Month],
Count([Category])/6 AS [6 Months Average]
FROM Incidents
WHERE (((Incidents.IncidentDate) Between DateAdd("m",-6,[Enter ending month
(6/1/2006)]) And [Enter ending month (6/1/2006)]))
GROUP BY [Enter ending month (6/1/2006)];

--
KARL DEWEY
Build a little - Test a little


:

I need some help writing this query. This should be simple, yet I can't get
it to work as I want. I have a simple database to keep track of incidents.
I need a count of the number of incidents each month (which I have) and for
each month I need the average number of incidents for the previous 6 months
(this is where I need help).

Ex data:

IncidentMonth CountofIncidents 6MoAvgIncidents
Oct-02 16 -
Nov-02 12 -
Dec-02 15 -
Jan-03 21 -
Feb-03 7 -
Mar-03 12 13.83333333
Apr-03 10 12.83333333
May-03 15 13.33333333
Jun-03 18 13.83333333
Jul-03 25 14.5
Aug-03 16 16
Sep-03 16 16.66666667
Oct-03 19 18.16666667
Nov-03 10 17.33333333
Dec-03 12 16.33333333


Here is my SQL:

SELECT Format([IncidentDate],"yyyy") AS SortbyYear,
Format([IncidentDate],"mm") AS SortbyMonth, Format([IncidentDate],"mmm-yy")
AS IncidentMonth, Count(Incidents.Category) AS CountOfIncidents, "HelpHere"
AS 6MoAvgIncidents
FROM Incidents
GROUP BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy"), "HelpHere"
ORDER BY Format([IncidentDate],"yyyy"), Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy");


Thanks in advance for any help offered!
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

To get:

IncidentMonth CountofIncidents 6MoAvgIncidents
Oct-02 16 -
Nov-02 12 -
Dec-02 15 -
Jan-03 21 -
Feb-03 7 -
Mar-03 12 13.83333333
Apr-03 10 12.83333333
May-03 15 13.33333333
Jun-03 18 13.83333333
Jul-03 25 14.5
Aug-03 16 16
Sep-03 16 16.66666667
Oct-03 19 18.16666667
Nov-03 10 17.33333333
Dec-03 12 16.33333333



Assuming the first field is REALLY a date field, not just a string, you can
use:

======================
SELECT a.incidentMonth, LAST(a.countOfIncidents), SUM(b.countOfIncidents)
/ 6

FROM tableNameHere AS a LEFT JOIN tableNameHere AS b
ON ( a.incidentMonth >= b.IncidentMonth
AND a.IncidentMonth <= DateAdd("m", 6, b.IncidentMonth)

GROUP BY a.incidentMonth
========================


Change the 6 by 12, two places, to get the average on the past 12 months.



Hoping it may help,
Vanderghast, Access MVP



Natalia said:
Hello,

I'm trying to use this solution to create a 12-month rolling average. The
math I need to do in the end is more complicated than a linear average; I
need to weight the numbers using values in another column. But that said,
I
tried using the code to calculate a linear average but the results I get
don't make sense.

This is the query I'm using:

SELECT DateAdd("m",-11,CVDate([EHSIRCalculationSmart].[YrMo])) & " - " &
DateAdd("m",1,CVDate([EHSIRCalculationSmart].[YrMo]))-1 AS [Month-Year],
Sum([EHSIRCalculationSmart1].[SumOfTotalPointsForEHSIR])/12 AS [Rolling 12
Months Average]
FROM EHSIRCalculationSmart, EHSIRCalculationSmart AS
EHSIRCalculationSmart1
WHERE (((CVDate([EHSIRCalculationSmart1].[YrMo])) Between
CVDate([EHSIRCalculationSmart].[YrMo]) And
DateAdd("m",-11,CVDate([EHSIRCalculationSmart].[YrMo]))))
GROUP BY CVDate([EHSIRCalculationSmart].[YrMo]),
DateAdd("m",-11,CVDate([EHSIRCalculationSmart].[YrMo])) & " - " &
CVDate([EHSIRCalculationSmart].[YrMo]);

The data I have, for example, sums up 20 incidents in January of 2006, and
17 in February of 2006, so I'd expect the first row to say 20/12 = 1.666;
instead it reads 35. Would this have something to do with the fact that
the
query I'm based on is itself a "totals" query?

Thanks in advance for the help!

Me :)

Chris said:
Thank you very much, this is what I was looking for. With a little
modification, it works as intended with my line graph. Thanks again.

KARL DEWEY said:
A little change ---
SELECT DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) &
" - "
& DateAdd("m",1,CVDate([Chris_Running_Mon].[Incident_Month]))-1 AS
[Month-Year],

--
KARL DEWEY
Build a little - Test a little


:

I think I did it in two queries as I do not know subqueries.
Chris_Running_Mon ---
SELECT Format([IncidentDate],"mm/\1/yyyy") AS Incident_Month,
Count(Incidents.Category) AS CountOfCategory
FROM Incidents
GROUP BY Format([IncidentDate],"mm/\1/yyyy");


SELECT DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) &
" - "
& CVDate([Chris_Running_Mon].[Incident_Month]) AS [Month-Year],
Sum([Chris_Running_Mon_1].[CountOfCategory])/6 AS [Running 6 Months
Average]
FROM Chris_Running_Mon, Chris_Running_Mon AS Chris_Running_Mon_1
WHERE (((CVDate([Chris_Running_Mon_1].[Incident_Month])) Between
CVDate([Chris_Running_Mon].[Incident_Month]) And
DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month]))))
GROUP BY CVDate([Chris_Running_Mon].[Incident_Month]),
DateAdd("m",-5,CVDate([Chris_Running_Mon].[Incident_Month])) & " - "
&
CVDate([Chris_Running_Mon].[Incident_Month]);

--
KARL DEWEY
Build a little - Test a little


:

I'll explain a little more what I'm doing here in case I'm missing
an easier
way to do this.

This data will be for a chart report--a simple line graph plotting
the
number of incidents for each month in a given year (selected from a
drop-down
box on a form).

Now I also want to add a trendline showing the 6 month rolling
average.
Using the trendling in the chart creator, the line doesn't start
until the
6th month on the graph. The first 6 months would draw data from
the previous
year.

I thought if I could build a query that matched up the 6month
average to
each particular month, then that column of data would create the
2nd line on
the graph.

Otherwise, how can I get the chart to show the line graph's
trendline to
extend from month 1-6?



:

Is this what you are looking for?
SELECT [Enter ending month (6/1/2006)] AS [Report Ending Month],
Count([Category])/6 AS [6 Months Average]
FROM Incidents
WHERE (((Incidents.IncidentDate) Between DateAdd("m",-6,[Enter
ending month
(6/1/2006)]) And [Enter ending month (6/1/2006)]))
GROUP BY [Enter ending month (6/1/2006)];

--
KARL DEWEY
Build a little - Test a little


:

I need some help writing this query. This should be simple,
yet I can't get
it to work as I want. I have a simple database to keep track
of incidents.
I need a count of the number of incidents each month (which I
have) and for
each month I need the average number of incidents for the
previous 6 months
(this is where I need help).

Ex data:

IncidentMonth CountofIncidents 6MoAvgIncidents
Oct-02 16 -
Nov-02 12 -
Dec-02 15 -
Jan-03 21 -
Feb-03 7 -
Mar-03 12 13.83333333
Apr-03 10 12.83333333
May-03 15 13.33333333
Jun-03 18 13.83333333
Jul-03 25 14.5
Aug-03 16 16
Sep-03 16 16.66666667
Oct-03 19 18.16666667
Nov-03 10 17.33333333
Dec-03 12 16.33333333


Here is my SQL:

SELECT Format([IncidentDate],"yyyy") AS SortbyYear,
Format([IncidentDate],"mm") AS SortbyMonth,
Format([IncidentDate],"mmm-yy")
AS IncidentMonth, Count(Incidents.Category) AS
CountOfIncidents, "HelpHere"
AS 6MoAvgIncidents
FROM Incidents
GROUP BY Format([IncidentDate],"yyyy"),
Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy"), "HelpHere"
ORDER BY Format([IncidentDate],"yyyy"),
Format([IncidentDate],"mm"),
Format([IncidentDate],"mmm-yy");


Thanks in advance for any help offered!
Click to expand...
Click to expand...
Click to expand...
 
M

Michael Gramelspacher

Sum([EHSIRCalculationSmart1].[SumOfTotalPointsForEHSIR])/12 AS [Rolling 12
Months Average]
Click to expand...

Maybe should be:

Sum([EHSIRCalculationSmart1].[SumOfTotalPointsForEHSIR])
/Count([EHSIRCalculationSmart1].[SumOfTotalPointsForEHSIR])
AS [Rolling 12 Months Average]

If your data starts in Jan 2006 with 20, then the average is 20/1 = 20
Add 17 more in February and the average is (20 +17)/2 = 18.5

Your query is murder on my eyes. You might think about alaising the table
names.

CREATE TABLE EHSIRCalculationSmart (
YrMo CHAR (7) NOT NULL UNIQUE
,SumOfTotalPointsForEHSIR DECIMAL (6,2) DEFAULT 0 NOT NULL);

INSERT INTO EHSIRCalculationSmart VALUES ('01-2006', 20);
INSERT INTO EHSIRCalculationSmart VALUES ('02-2006', 17);
INSERT INTO EHSIRCalculationSmart VALUES ('03-2006', 21);
INSERT INTO EHSIRCalculationSmart VALUES ('04-2006', 19);
INSERT INTO EHSIRCalculationSmart VALUES ('05-2006', 23);
INSERT INTO EHSIRCalculationSmart VALUES ('06-2006', 15);
INSERT INTO EHSIRCalculationSmart VALUES ('07-2006', 14);
INSERT INTO EHSIRCalculationSmart VALUES ('08-2006', 15);
INSERT INTO EHSIRCalculationSmart VALUES ('09-2006', 17);
INSERT INTO EHSIRCalculationSmart VALUES ('10-2006', 19);
INSERT INTO EHSIRCalculationSmart VALUES ('11-2006', 21);
INSERT INTO EHSIRCalculationSmart VALUES ('12-2006', 23);
INSERT INTO EHSIRCalculationSmart VALUES ('01-2007', 23);
INSERT INTO EHSIRCalculationSmart VALUES ('02-2007', 24);
INSERT INTO EHSIRCalculationSmart VALUES ('03-2007', 22);

SELECT DateAdd("m",-11,CVDate([E].[YrMo])) & " - "
& DateAdd("m",1,CVDate([E].[YrMo]))-1 AS [Month-Year],
Sum([E1].[SumOfTotalPointsForEHSIR])
/Count([E1].[SumOfTotalPointsForEHSIR])
AS [Rolling 12 Months Average]
FROM EHSIRCalculationSmart AS E, EHSIRCalculationSmart AS E1
WHERE (((CVDate([E1].[YrMo])) Between CVDate([E].[YrMo])
And DateAdd("m",-11,CVDate([E].[YrMo]))))
GROUP BY CVDate([E].[YrMo]), DateAdd("m",-11,CVDate([E].[YrMo]))
& " - " & CVDate([E].[YrMo]);

Month-Year Rolling 12 Months Average
2/1/2005 - 1/31/2006 20.00
3/1/2005 - 2/28/2006 18.50
4/1/2005 - 3/31/2006 19.33
5/1/2005 - 4/30/2006 19.25
6/1/2005 - 5/31/2006 20.00
7/1/2005 - 6/30/2006 19.17
8/1/2005 - 7/31/2006 18.43
9/1/2005 - 8/31/2006 18.00
10/1/2005 - 9/30/2006 17.89
11/1/2005 - 10/31/2006 18.00
12/1/2005 - 11/30/2006 18.27
1/1/2006 - 12/31/2006 18.67
2/1/2006 - 1/31/2007 18.92
3/1/2006 - 2/28/2007 19.50
4/1/2006 - 3/31/2007 19.58
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Rolling Average Calculation 4
Cross tab query solution 4
Numbers from query concactinate when added in Report 1
rolling 3 months query 3
Display billing month based on start date and end date 5
running sum in a query for a calculated field 14
Need help with SQL Statement in MS Access 2003 Chart 11
Totals query per day per month 1

Top