New Sample: RollingAverages.MDB

RollingAverages.MDB

by Roger Carlson

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.

 You can find the sample here: RollingAverages.mdb.

.