Update table with rolling average from itself

Question

I'm trying to update a temp table with a rolling average calculation (MS Access 2010.)

As a select query, this works to calculate a 3 month rolling average but is slow so I'd rather have the values stored and updated only when necessary:

SELECT tempQORDistGrouped.Type, tempQORDistGrouped.Supplier, tempQORDistGrouped.DepBkMo, tempQORDistGrouped.Amt, tempQORDistGrouped.Brands, tempQORDistGrouped.T2, tempQORDistGrouped.Brand, (select avg(rolavg.Amt) from tempQORDistGrouped as rolavg 
  where rolavg.Type = tempQORDistGrouped.Type
  and rolavg.Supplier = tempQORDistGrouped.Supplier
  and rolavg.Brands = tempQORDistGrouped.Brands
  and rolavg.Brand = tempQORDistGrouped.Brand
  and rolavg.DepBkMo between dateadd("m",-2,tempQORDistGrouped.DepBkMo) and tempQORDistGrouped.depbkmo) AS AvgAmt
FROM tempQORDistGrouped;

I've tried the update query below but I think my inner join syntax is bad as it won't recognize x1.Type as a valid field (do I need to include these as part of the inner join fields rather than in the where clause??):

UPDATE tempQORDistGrouped AS x1 
INNER JOIN (SELECT itmID,  avg(Amt) AS RolAvg
  FROM tempQORDistGrouped 
  WHERE tempQORDistGrouped.Type = x1.Type
  AND tempQORDistGrouped.Brand = x1.Brand
  AND tempQORDistGrouped.Brands = x1.Brands
  AND tempQORDistGrouped.T2 = x1.T2
  AND tempQORDistGrouped.DepBkMo between dateadd("m",-2,x1.DepBkMo) and x1.DepBkMo 
  GROUP BY itmID
  ) AS x2
ON x1.itmID = x2.itmID
SET x1.3MonthRollingAmt = x2.RolAvg;

Cheers

SoulTrain SoulTrain · Accepted Answer · 2014-04-01T20:34:23

Untested but should work. I am doing a column level query to calculated the AVG and then mapping it back to the rest of the columns

Try this:

UPDATE tempQORDistGrouped AS x1
INNER JOIN (
    SELECT itmID
        , (
            SELECT avg(Amt) amt
            FROM tempQORDistGrouped x4
            WHERE x4.Type = x3.Type
                AND x4.Brand = x3.Brand
                AND x4.Brands = x3.Brands
                AND x4.T2 = x3.T2
                AND x4.DepBkMo BETWEEN dateadd("m", - 2, x3.DepBkMo) AND x3.DepBkMo
            ) AS RolAvg
        , x3.Brand
        , x3.Brands
        , x3.DepBkMo
        , x3.T2
    FROM tempQORDistGrouped x3
    ) AS x2
    ON x1.itmID = x2.itmID
        AND x1.Brand = x2.Brand
        AND x1.Brands = x2.Brands
        AND x1.T2 = x2.T2
        AND x1.DepBkMo BETWEEN dateadd("m", - 2, x2.DepBkMo) AND x2.DepBkMo    
SET x1.3MonthRollingAmt = x2.RolAvg;

Update table with rolling average from itself

1 Answers