Calculate a Moving Average and Update Table

[SuzK]

I am trying to calculate in VBA in Access 2002 a moving average and update a table with the calculations. Fields in my WeeklyData table are
Week Ending (date)
ItemNbr (double)
Sales Dollars (double)
Sales Units (double)
Promo (Text) -- is null or "X"
AvgWklyDollars (double)
AvgWklyUnits (double)

I have a vba module which I thought would work, but it doesn't. I think the problem is an embedded SQL Totals Top 8 query, which doesn't work when I tried it as an Access query. HELP!

For each week and for each ItemNbr, I want to calculate the average of the Last 8 weeks, where Promo <> "X" . Where Promo Is Null I want to copy the prior week's averages. Finally, I want to update my table "WeeklySalesbytypeAllItems" with this calculated AvgWkly data.

Here's the code:

Function DateSQL(pvarDate) As String
' Create a string representation suitable for SQL
' Jet accepts only US dates or ISO dates
 
    If IsNull(pvarDate) Then
        DateSQL = "Null"
    Else
        DateSQL = Format(pvarDate, "\#yyyy\-mm\-dd\#")
    End If
 
End Function
 
Sub ComputeAllAverages()
' Calculate moving averages for sales and units in the weekly sales table
' A single total query is used to calculate both for each record
 
    Const SALES = "WeeklySalesbytypeAllItems"   
    Const SCOPE = 8                           ' nb of weeks in moving average
 
    Dim mdb As DAO.Database
    Dim recMain As DAO.Recordset
    Dim recTot As DAO.Recordset
    Dim strSQL As String
 
    Set mdb = CurrentDb
    Set recMain = mdb.OpenRecordset(SALES, dbOpenDynaset)
    Do Until recMain.EOF
 
        ' build query
        strSQL _
            = " SELECT" _
            & " Count(*) AS Nb," _
            & " Sum([Sales$]) AS TotalSales," _
            & " Sum([Units]) AS TotalUnits" _
            & " FROM (" _
            & "     SELECT TOP " & SCOPE & " [Sales$], [Units]" _
            & "     FROM [" & SALES & "]" _
            & "     WHERE DPCI = " & recMain!ItemNbr_
            & "     And [Week Ending] <= " & DateSQL(recMain![Week Ending]) _
            & "     And Promo Is Null" _
            & "     ORDER BY [Week Ending] Desc" _
            & " ) AS WSAI"
        Set recTot = mdb.OpenRecordset(strSQL, dbOpenSnapshot)
 
        ' store results
        recMain.Edit
        ' If recTot!Nb <> SCOPE Then   ' strict moving average
        If recTot!Nb > 0 Then   ' lenient moving average
            recMain!AvgWklyDollars = Null
            recMain!AvgWklyUnits = Null
        Else
            recMain!AvgWklyDollars = recTot!TotalSales / recTot!Nb
            recMain!AvgWklyUnits = recTot!TotalUnits / recTot!Nb
        End If
        recMain.Update
 
        recMain.MoveNext
    Loop
End Sub
 

Here's sample data from the WeeklySalesbytypeAllItems table.

ItmNbr Week Ending Sales$ Units Promo
10025 9/15/2007 11349 2229
10025 9/22/2007 13593 2729
10025 9/29/2007 12709 2556
10025 10/6/2007 10104 1911
10025 10/13/2007 8693 1586
10025 10/20/2007 8712 1589
10025 10/27/2007 8532 1552
10025 11/3/2007 9553 1733
10025 11/10/2007 9713 1765
10025 11/17/2007 14827 2918
10025 11/24/2007 13938 2802 X
10025 12/1/2007 14102 2836 X
10025 12/8/2007 11052 2090
10025 12/15/2007 12308 2274
10025 12/22/2007 11405 2112
10025 12/29/2007 12624 2489
10025 1/5/2008 11978 2412 X
10025 1/12/2008 11550 2325 X
10025 1/19/2008 9938 1875
10044 9/15/2007 4211 4265
10044 9/22/2007 3965 4017
10044 9/29/2007 4124 4180
10044 10/6/2007 3388 3938
10044 10/13/2007 3768 3816
10044 10/20/2007 3881 3934
10044 10/27/2007 3692 3742
10044 11/3/2007 3654 3702
10044 11/10/2007 3429 3475 X
10044 11/17/2007 3756 3809
10044 11/24/2007 3869 3925
10044 12/1/2007 4308 4367
10044 12/8/2007 4490 4553
10044 12/15/2007 5516 5592
10044 12/22/2007 8016 8119
10044 12/29/2007 5097 5169
10044 1/5/2008 3608 3661
10044 1/12/2008 4121 4179 X
10044 1/19/2008 4259 4317