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...
SuzK
Last Activity: Mar 19 '08, 01:02 PM
Joined: Feb 22 '08
Location:
-
Calculate a Moving Average and Update Table
-
Thanks... I already have the averages for all items. My problem is not in calculating the averages -- it is updating the table to copy the previous item weekly average when the week is a promo week. A promo week is identified in my table's field "Promo", where "Promo" = "X". In my Access query, I calculated the weekly averages where Promo <> "X".
Again, thanks for your help. I'll work... -
Thanks, VBWheaties for your patience. My logic is "based on a backwards read of a subset of records". My VBA coding skills are extremely limited. I have the calculations working in Excel where each subgroup is on a separate worksheet, but I need to move to Access because of the increasing number of worksheets.
I have created a working moving average custom function in Access (referenced earlier) which I use in a Make...Leave a comment:
-
Thanks again for your help and patience.... but I'm still need help with coding an "IF" scenario. If you'll look at my desired results for the "Base$", you'll see that if the value of "promo" is null, I want to calculate Base$ as a moving 8-week average. However, if the value of "promo" is "X", I want to copy the previous record's value for Base$. See an example of my expected results for Base$,...Leave a comment:
-
Thank you for your reply. Your assumption is correct. I'm not very adapt at VBA coding. Can you provide the code to accomplish your suggestion? I'm trying to learn VBA on my own, but I seem to have a mental block on the steps involved in setting variables and then manipulating them....Leave a comment:
-
How do a copy a field value from a previous record based on criteria?
In Access 2002, I have created a Make Table query to add a new field “Base$” through a custom function, moving average, that averages each 8 weeks of data. However, I also need to calculate a different result if there is an entry “X” in the Flag field--where there is a Flag "X", I want to duplicate the 8-week average number of the previous record. What is the VBA code to provide that alternate result? Here is a sample of the...
No activity results to display
Leave a comment: