Roger's Access Library Homepage
Forum Home Forum Home > Roger's Access Library > Roger's Download Samples
  New Posts New Posts RSS Feed - RollingAverages.MDB
  FAQ FAQ  Forum Search   Events   Register Register  Login Login


Please consider donating $1 per sample downloaded, (find out why here)

RollingAverages.MDB

 Post Reply Post Reply
Author
Message
Roger Carlson View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 20 Feb 2008
Status: Offline
Points: 238
Post Options Post Options   Thanks (0) Thanks(0)   Quote Roger Carlson Quote  Post ReplyReply Direct Link To This Post Topic: RollingAverages.MDB
    Posted: 25 Jun 2010 at 11:13am
Please consider donating $1 per sample downloaded, find out why here.
 
RollingAverages.MDB
 
This sample illustrates two different ways to create a "Rolling Average" query. It averages the latest 13 months in the sequence.
 
Sequence tWeek tValue RollingAverage
20 Week 26 10 39.8461538461538
19 Week 25 11 43.8461538461538
18 Week 24 22 48.4615384615385
17 Week 23 44 47.6923076923077
16 Week 22 55 45.4615384615385
15 Week 21 44 46.1538461538462
14 Week 20 22 45.4615384615385
13 Week 19 77 48.5384615384615
12 Week 18 88 46.1666666666667
11 Week 17 11 42.3636363636364
10 Week 16 74 45.5
9 Week 15 35 42.3333333333333
8 Week 14 25 43.25
7 Week 13 62 45.8571428571429
6 Week 12 71 43.1666666666667
5 Week 11 12 37.6
4 Week 10 15 44
3 Week 9 64 53.6666666666667
2 Week 8 35 48.5
1 Week 7 62 62
 
The first two use DCount and the second two use a Sub Query.  You must identify a unique column in the query to create the sequence on.  I used an autonumber ID field, which while in order, has gaps in it. 

The DCount method creates an updateable recordset.  The SubQuery method produced non-updateable recordsets.

Download Access 2000-2007 (mdb): RollingAverage.zip
Download Access 2007-2010 (accdb): RollingAverage07.zip
 
Please consider donating $1 per sample downloaded, find out why here.<
Back to Top
Sponsored Links


Back to Top
Roger Carlson View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 20 Feb 2008
Status: Offline
Points: 238
Post Options Post Options   Thanks (0) Thanks(0)   Quote Roger Carlson Quote  Post ReplyReply Direct Link To This Post Posted: 30 Dec 2010 at 9:41am
Update: Added detailed documentation
Please consider donating $1 per sample downloaded, find out why here.<
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down