[AccessD] Moving Average

If you want to do through vba code
ACC2000: How to Compute Moving Averages in Visual Basic for Applications
http://support.microsoft.com/default.aspx?scid=kb;en-us;210138
I would probably do this inside arrays for easier manipulation rather 
than SQL but then I am old Fortan guy.

Just remember you can also do an exponential moving average, it  puts 
more weight toward recent data and
less weight toward past data than does the simple moving average 
method.  This method is often
called exponentially weighted.
The major statistical methods with this are called Box-Jenkins.
One of the better time series forecasting tools complete with vba 
callable api is
AutoBox. But Oh my it is written in Fortran. It is around $400.

http://www.autobox.com/autobox.htm


A.D.Tejpal wrote:

>Chester,
>
>    Sample query given below, should get the moving average (for three months) of field named Qty in table T_MA. SMonth is number type field representing month.
>
>    It is presumed that there are no holes in month values. Otherwise, you will have to get the running count of months in a preliminary query and use that as the source for final query.
>
>A.D.Tejpal
>--------------
>
>=====================================
>SELECT T_MA.*, (Select Avg(Qty) From T_MA AS T1 Where T1.SMonth >= T_MA.SMonth - 2 And T1.SMonth <= T_MA.SMonth) AS MovingAvg  
>FROM T_MA  
>ORDER BY T_MA.SMonth;
>=====================================
>
>  ----- Original Message ----- 
>  From: Kaup, Chester 
>  To: Access Developers discussion and problem solving 
>  Sent: Wednesday, September 07, 2005 23:37
>  Subject: Re: [AccessD] Moving Average
>
>
>  This works great for a running average but guess I did not make myself clear. What I need is a moving 3 month average. For example if I am on record 3 average records 1, 2 and 3. If I am on record 4 average records 2, 3 and 4 etc. Thanks.
>
>  -----Original Message-----
>  From: accessd-bounces at databaseadvisors.com
>  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
>  Sent: Wednesday, September 07, 2005 12:22 PM
>  To: Access Developers discussion and problem solving
>  Subject: Re: [AccessD] Moving Average
>
>  Chester,
>
>      For computing a running result (e.g. Count/Sum/Avg etc), the order in which the fields are sorted, has also to be taken into account.
>  Amongst such fields, last one should be the one serving as primary key. This is meant to prevent likely overlapping values.
>
>      Sample query given below, gets the running average for field named Qty (number type) in table T_Data, sorted on fields SDate (date type) and ID (primary key).
>
>  Best wishes, 
>  A.D.Tejpal
>  --------------
>
>  =================================
>  SELECT T_Data.*, (Select Avg(Qty) From T_Data As T1 Where T1.SDate <
>  T_Data.SDate Or (T1.SDate = T_Data.SDate And T1.ID <=  T_Data.ID)) AS
>  RunningAvg
>  FROM T_Data
>  ORDER BY T_Data.SDate, T_Data.ID;
>  =================================
>
>    ----- Original Message ----- 
>    From: Kaup, Chester 
>    To: Access Developers discussion and problem solving 
>    Sent: Wednesday, September 07, 2005 21:37
>    Subject: [AccessD] Moving Average
>
>    I used to have a function to do a moving average in a query but I cannot find it now. Does someone have one they are willing to share? I need to do one for 3 months. Thanks 
>
>    Chester Kaup
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada