Prev: Pass a parameter to nested query
Next: Expression using RIGHT and LEN functions
From: J. Johnstone on 17 Feb 2005 11:11
Could someone help me with a query to calculate the rolling average of
values? I have columns in my table of Year, Month and Value. I am trying
to calculate the 12 month rolling average of the Value. This is what the
ouput should look like with "12MoAvg" being the rolling average. How do I do
this in Access?


Year Month Value 12MoAvg
2003 1 10
2003 2 20
2003 3 30
2003 4 35
2003 5 50
2003 6 51
2003 7 34
2003 8 67
2003 9 44
2003 10 36
2003 11 65
2003 12 49 40.92
2004 1 52 44.42
2004 2 6 43.25
2004 3 2 40.92
2004 4 39 41.25
2004 5 41 40.50
2004 6 30 38.75
2004 7 46 39.75
2004 8 32 36.83
2004 9 61 38.25
2004 10 47 39.17
2004 11 37 36.83
2004 12 50 36.92

Thanks,
--
J. Johnstone
From: Duane Hookom on 17 Feb 2005 12:07
Try starting with something like:
SELECT tblJohnstone.*,
(SELECT AVG(Value)
FROM tblJohnstone j
WHERE DateDiff("m",DateSerial(j.Year,j.month,1),
DateSerial(tblJohnstone.Year,tblJohnstone.Month,1))
Between 0 and 11) AS MA12
FROM tblJohnstone;

--
Duane Hookom
MS Access MVP
--

"J. Johnstone" <JJohnstone(a)discussions.microsoft.com> wrote in message
news:F38DC8C6-E635-44AB-9DE3-63B72C9E27D1(a)microsoft.com...
> Could someone help me with a query to calculate the rolling average of
> values? I have columns in my table of Year, Month and Value. I am
> trying
> to calculate the 12 month rolling average of the Value. This is what the
> ouput should look like with "12MoAvg" being the rolling average. How do I
> do
> this in Access?
>
>
> Year Month Value 12MoAvg
> 2003 1 10
> 2003 2 20
> 2003 3 30
> 2003 4 35
> 2003 5 50
> 2003 6 51
> 2003 7 34
> 2003 8 67
> 2003 9 44
> 2003 10 36
> 2003 11 65
> 2003 12 49 40.92
> 2004 1 52 44.42
> 2004 2 6 43.25
> 2004 3 2 40.92
> 2004 4 39 41.25
> 2004 5 41 40.50
> 2004 6 30 38.75
> 2004 7 46 39.75
> 2004 8 32 36.83
> 2004 9 61 38.25
> 2004 10 47 39.17
> 2004 11 37 36.83
> 2004 12 50 36.92
>
> Thanks,
> --
> J. Johnstone


From: J. Johnstone on 17 Feb 2005 14:05
THANK YOU!!! You solved the problem!!!

"Duane Hookom" wrote:

> Try starting with something like:
> SELECT tblJohnstone.*,
> (SELECT AVG(Value)
> FROM tblJohnstone j
> WHERE DateDiff("m",DateSerial(j.Year,j.month,1),
> DateSerial(tblJohnstone.Year,tblJohnstone.Month,1))
> Between 0 and 11) AS MA12
> FROM tblJohnstone;
>
> --
> Duane Hookom
> MS Access MVP
> --
>
> "J. Johnstone" <JJohnstone(a)discussions.microsoft.com> wrote in message
> news:F38DC8C6-E635-44AB-9DE3-63B72C9E27D1(a)microsoft.com...
> > Could someone help me with a query to calculate the rolling average of
> > values? I have columns in my table of Year, Month and Value. I am
> > trying
> > to calculate the 12 month rolling average of the Value. This is what the
> > ouput should look like with "12MoAvg" being the rolling average. How do I
> > do
> > this in Access?
> >
> >
> > Year Month Value 12MoAvg
> > 2003 1 10
> > 2003 2 20
> > 2003 3 30
> > 2003 4 35
> > 2003 5 50
> > 2003 6 51
> > 2003 7 34
> > 2003 8 67
> > 2003 9 44
> > 2003 10 36
> > 2003 11 65
> > 2003 12 49 40.92
> > 2004 1 52 44.42
> > 2004 2 6 43.25
> > 2004 3 2 40.92
> > 2004 4 39 41.25
> > 2004 5 41 40.50
> > 2004 6 30 38.75
> > 2004 7 46 39.75
> > 2004 8 32 36.83
> > 2004 9 61 38.25
> > 2004 10 47 39.17
> > 2004 11 37 36.83
> > 2004 12 50 36.92
> >
> > Thanks,
> > --
> > J. Johnstone
>
>
>
 | 
Pages: 1
Prev: Pass a parameter to nested query
Next: Expression using RIGHT and LEN functions