Print Page | Close Window

RollingAverages.MDB

Printed From: Roger's Access Library
Category: Roger's Access Library
Forum Name: Roger's Download Samples
Forum Description: A compendium of Microsoft Access 97 and 2000+ routines, each separated into its own sample database.
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=544
Printed Date: 24 Oct 2017 at 7:30am


Topic: RollingAverages.MDB
Posted By: Roger Carlson
Subject: RollingAverages.MDB
Date Posted: 25 Jun 2010 at 11:13am
Please consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm - 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): http://www.rogersaccesslibrary.com/forum/uploads/5/RollingAverage.zip - RollingAverage.zip
Download Access 2007-2010 (accdb): uploads/5/RollingAverage07.zip - RollingAverage07.zip
 


-------------
Consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<



Replies:
Posted By: Roger Carlson
Date Posted: 30 Dec 2010 at 9:41am
Update: Added detailed documentation

-------------
Consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<



Print Page | Close Window