[AccessD] Moving Average

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