[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

  Engineering Technician

  Kinder Morgan CO2 Company, LLP

  Office (432) 688-3797

  FAX (432) 688-3799