floating / moving averages

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

Hello, I am looking for an elegant way to determine a
floating average in a query.

Consider the following;

Table X { Date theDate, Number someNum }

How can I say average someNum over a *two month* period by
*month*? Is the best way to do this in VBA or possibly
using DSUM or something else?
 
This is a Query news group so try a query:
SELECT Format([theDate],"yyyymm") as YM, Avg([SomeNum]) as TheAverage
FROM X
GROUP BY Format([theDate],"yyyymm");
 
The query mentioned in the previous post will average over
a one month period for each month. Instead, I need to
query over a *two( month period for each month.

Something like,

SELECT Format([theDate],"yyyymm") as YM, ( calculate
average over last two months ([SomeNum])) as
TheTwoMonthFloatingAverage
FROM X
GROUP BY Format([theDate],"yyyymm");

-----Original Message-----
This is a Query news group so try a query:
SELECT Format([theDate],"yyyymm") as YM, Avg([SomeNum]) as TheAverage
FROM X
GROUP BY Format([theDate],"yyyymm");

--
Duane Hookom
MS Access MVP


JimS said:
Hello, I am looking for an elegant way to determine a
floating average in a query.

Consider the following;

Table X { Date theDate, Number someNum }

How can I say average someNum over a *two month* period by
*month*? Is the best way to do this in VBA or possibly
using DSUM or something else?
Click to expand...


.
Click to expand...
 
I can see how this gets me an average for every 2nd month
and its preceding two months. I suppose if I wanted the
two month average *every* month it would be simple enough
to union the below query with the another query exactly
like below except replace occurrences of (month(thedate)-1)
\2 with (month(thedate))\2

Thanks for responses Duane.

- Jim

-----Original Message-----
Sorry I missed the "two".

SELECT Format([theDate],"yyyy") as Yr, (Month([theDate])- 1)\2 as Mth,
Avg([SomeNum])) as TheTwoMonthFloatingAverage
FROM X
GROUP BY Format([theDate],"yyyy"), (Month([theDate])-1)\2;

Make sure you use \ rather than /.
--
Duane Hookom
MS Access MVP


Jims said:
The query mentioned in the previous post will average over
a one month period for each month. Instead, I need to
query over a *two( month period for each month.

Something like,

SELECT Format([theDate],"yyyymm") as YM, ( calculate
average over last two months ([SomeNum])) as
TheTwoMonthFloatingAverage
FROM X
GROUP BY Format([theDate],"yyyymm");

-----Original Message-----
This is a Query news group so try a query:
SELECT Format([theDate],"yyyymm") as YM, Avg([SomeNum]) as TheAverage
FROM X
GROUP BY Format([theDate],"yyyymm");

--
Duane Hookom
MS Access MVP


Hello, I am looking for an elegant way to determine a
floating average in a query.

Consider the following;

Table X { Date theDate, Number someNum }

How can I say average someNum over a *two month*
Click to expand...
period
by
*month*? Is the best way to do this in VBA or possibly
using DSUM or something else?






.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top