Loop to do rolling 12 month average

N

nguyeda

Registered User.
Local time
Today, 14:29
Joined
May 11, 2011
Messages
37
May be VBA is a better solution for this:

Say for Material A: I need Access to see that the 1st date is 05/01/2013 and say 365 days out or 12 months (05/01/2013-04/01/2014) the average is 158.6 then 06/2013-05/2014 the average is 146.667 and so on.

Every month is not going to be listed in the results, if there is no month then assume 0.

Sample data attached.
 

Attachments

One way to obtain the average between two selected dates and for a certain material.
 

Attachments

Thanks but not what I'm looking for. I need an average for every rolling 12 month period so each month would return an average of the future 12 months. There are really over 10,000 materials.
 
Thanks again but still not there! Its better that it does all the materials now but the result I'm looking for is more like

Completely fictional:

A - 8/1/2013 - 30 (which is the average of 8/1/2013-7/1/2013)
A - 9/1/2013 - 40 (which is the average of 9/1/2013-8/1/2013)
A - 10/1/2013 - 50 (which is the average of 10/1/2013-9/1/2013)
etc
B - 8/1/2013 - 30 (which is the average of 8/1/2013-7/1/2013)
B - 9/1/2013 - 40 (which is the average of 9/1/2013-8/1/2013)
B - 10/1/2013 - 50 (which is the average of 10/1/2013-9/1/2013)
etc
C- 8/1/2013 - 3 (which is the average of 8/1/2013-7/1/2013)
C - 9/1/2013 - 4 (which is the average of 9/1/2013-8/1/2013)
C - 10/1/2013 - 7 (which is the average of 10/1/2013-9/1/2013)
etc
 
A - 8/1/2013 - 30 (which is the average of 8/1/2013-7/1/2013)
Click to expand...
Including the comment (bold text) ?

And you don't say at what date to start.
 
Not including the comment box. Starting with the date of the first demand for each item.

So if the first demand was in May, start there and avg for 12 months then the following months.
 
Is doable but involve a lot of VBA lines (and skill).
It isn't a "ten minutes" task.
 
You must log in or register to reply here.

Similar threads

P
Rolling 12 Months Total
Replies
6
Views
856
arnelgp
arnelgp
K
Total and Average of Inventory Sold by rolling Daily, 30Day, 90Day, 365Day
Replies
9
Views
979
CJ_London
C
B
DoCmd.RunCommand acCmdPaste stopped working, think office 365 update
Replies
3
Views
451
bignose2
B
M
Cont from my previous post
Replies
1
Views
228
Gasman
Gasman
P
How to Sequential Numbering that includes Month Year and resets every year
Replies
17
Views
1,847
apr pillai
apr pillai

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom