There are many ways to skin this cat... {meeerrrrroooowww!}
Your problem is that you have different time bases over which to examine subsets of the
same exact data. Worse, they are OVERLAPPING subsets. Access is not going to like that too well, but there are ways to 'divide and conquer' this problem.
First, to SELECT the data you want, you need a query based on DateDiff. Look up DateDiff, which will allow you to compute the age of something. You can compare the date of your individual account's old value records to Now() or Date() (either of which is the current date, with or without a time field respectively). DateDiff lets you specify units. If you specify
months because your data is recorded on a monthly basis, then your filters can be set to select for age <= 120 months, <=60 months, or <=36 months for your 10-year, 5-year, and 3-year samples. respectively. (If the accounts update daily or weekly, you use different time units, but you get the idea.)
The problem you will have is that if you are looking at data from the same table, you might confuse Access if you try to do this all in one shot. The three different filtration values would result in some zeros in inappropriate places if you tried to do this in a single query. So build three queries, one for each date range. This will give you the three sets of aggregates in three separate queries.
NOW, here is where some of my fellow Access designers might gasp... OK folks, read it through before you go nuts...
You can build a separate table that contains the required fields for your account numbers and appropriate aggregates. Besides the account ID, you will need three sets of fields per account for the three time ranges. One field in each set for each aggregate. If all you want is averages, one field per set. If you want min + max, two more fields per set. Need counts, another field, and so on... In this table, use suffixes of 10, 5, and 3, maybe.
Pre-erase the separate table. (Write an Erase query.) Using the 10-year query, Insert records for the individual accounts and fill in the fields corresponding to the 10-year set. Now go back using the 5-year and 3-year queries to do Updates to the appropriate alternate field sets. Each of these queries will write complete aggregate data to one of the three field sets. When you are done, you can have a table that lists each account and has three sets of fields (individually updated) that give you your "moving window" averages.
You can build a macro that will run the SQL in sequence so that you do the erase, insert, and two updates as a single action. While folks rightfully don't like macros, this is one case where they do the right thing very simply.
NOW, before anyone goes berserk out there, some appropriate explanation and warnings...
Be aware that this is a
temporary table used for
report generation only and it has absolutely
no validity for any purpose other than reporting. Further, it is
not normalized. (Technically, the sliding windows represent something called a repeating group, which is a violation of normalization.) If you take this approach, you take it as a
workaround for the difficulty of making the same query and same set of aggregates work correctly for three different time ranges in a single query. I don't think even a CrossTab query would do this one quite right.
Be PAINFULLY aware that this table is NOT your live data. It is at best a snapshot of the status quo at a particular time. It is a workaround. A stop-gap measure. A 'baling wire and spit' job. NEVER base anything except your report off this table.
However, this will give you a table you can use to drive a report that lists your accounts down the page and sliding-window averages across the page. Remember to rebuild this table EVERY TIME YOU UPDATE ANY ACCOUNT! Because otherwise it has no meaning at all. In fact, to protect yourself from making mistakes, erase it when you are done with your reports.
