By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 826 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

Calculate a Moving Average and Update Table

P: 7
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:

Expand|Select|Wrap|Line Numbers
  1. Function DateSQL(pvarDate) As String
  2. ' Create a string representation suitable for SQL
  3. ' Jet accepts only US dates or ISO dates
  4.  
  5.     If IsNull(pvarDate) Then
  6.         DateSQL = "Null"
  7.     Else
  8.         DateSQL = Format(pvarDate, "\#yyyy\-mm\-dd\#")
  9.     End If
  10.  
  11. End Function
  12.  
  13. Sub ComputeAllAverages()
  14. ' Calculate moving averages for sales and units in the weekly sales table
  15. ' A single total query is used to calculate both for each record
  16.  
  17.     Const SALES = "WeeklySalesbytypeAllItems"   
  18.     Const SCOPE = 8                           ' nb of weeks in moving average
  19.  
  20.     Dim mdb As DAO.Database
  21.     Dim recMain As DAO.Recordset
  22.     Dim recTot As DAO.Recordset
  23.     Dim strSQL As String
  24.  
  25.     Set mdb = CurrentDb
  26.     Set recMain = mdb.OpenRecordset(SALES, dbOpenDynaset)
  27.     Do Until recMain.EOF
  28.  
  29.         ' build query
  30.         strSQL _
  31.             = " SELECT" _
  32.             & " Count(*) AS Nb," _
  33.             & " Sum([Sales$]) AS TotalSales," _
  34.             & " Sum([Units]) AS TotalUnits" _
  35.             & " FROM (" _
  36.             & "     SELECT TOP " & SCOPE & " [Sales$], [Units]" _
  37.             & "     FROM [" & SALES & "]" _
  38.             & "     WHERE DPCI = " & recMain!ItemNbr_
  39.             & "     And [Week Ending] <= " & DateSQL(recMain![Week Ending]) _
  40.             & "     And Promo Is Null" _
  41.             & "     ORDER BY [Week Ending] Desc" _
  42.             & " ) AS WSAI"
  43.         Set recTot = mdb.OpenRecordset(strSQL, dbOpenSnapshot)
  44.  
  45.         ' store results
  46.         recMain.Edit
  47.         ' If recTot!Nb <> SCOPE Then   ' strict moving average
  48.         If recTot!Nb > 0 Then   ' lenient moving average
  49.             recMain!AvgWklyDollars = Null
  50.             recMain!AvgWklyUnits = Null
  51.         Else
  52.             recMain!AvgWklyDollars = recTot!TotalSales / recTot!Nb
  53.             recMain!AvgWklyUnits = recTot!TotalUnits / recTot!Nb
  54.         End If
  55.         recMain.Update
  56.  
  57.         recMain.MoveNext
  58.     Loop
  59. End Sub
  60.  
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
Mar 4 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.