How do I create a weekly rolling average in access?

[Guest]

I have created an access database, and have turned dates into weekly periods,
but I cannot find a way to create a query to calculate a weekly rolling
average of my data, for 2 week, 3 week, 4 week, and 12 week periods. I also
need it to calculate a 12 week average even if there are only 9, 10, or 11
weeks of data, based on the weeks which data is available for. This is a
really simple exercise in excel, but because of the format used in access, it
seems really difficult. Can anyone please help me?

 

[John Spencer]

Without a lot more information about your data structure you probably won't
get an answer.

At a minimum post the SQL text for a query that gives you all the
information you want other than the rolling average. Tell us which fields
are used to determine weekly periods and their field types.

 

[Guest]

Thanks John, appreciate the advice.

I am trying to run this off a query which has the following fields, using
some test sample data. I need it to calculate a rolling average of the
"sumofpips made" for each week for each trader, for 2 week, 3 week, 4 week,
and 12 week periods. It also needs to have 12 week averages, even for the
first few weeks before there is 12 weeks of data, averaging whatever number
of weeks is actually available. The same for the other time frames.

Trader SumOfPips made Week
Anth 100 3
Test 98 1
Test 200 2
Test 22 3
Test 109 4
Test 100 5
Test 309 6
Test 56 7
Test 125 8
Test 236 9
Test 91.2 10
Test 24 11
Test 100 12
Test 100 13

 

[John Spencer]

You could try using correlated subqueries. I think the following UNTESTED
SQL may give you the idea of what you need.

SELECT Trader, Week,
(SELECT Avg(SumOfPips)
FROM TheTable As T
WHERE T.Trader = TheTable.Trader
AND T.Week <= TheTable.Week
AND T.Week >TheTable.Week - 12) as RollingAvg12,
(SELECT Avg(SumOfPips)
FROM TheTable As T
WHERE T.Trader = TheTable.Trader
AND T.Week <= TheTable.Week
AND T.Week >TheTable.Week - 4) as RollingAvg4
FROM TheTable