Previous Week and Rolling Average

[elephant97]

Here is my data set:

Date---------FTMR-------Outstanding---Avg Age
5/13/04-----83.70%-----626-------------25.7
5/20/04-----85.60%-----600-------------26.1
5/27/04-----83.30%-----623-------------26.8
6/3/04------85.50%-----647-------------25.1
6/10/04-----85.20%-----664-------------26.9
6/17/04-----85.10%-----660-------------28.3
6/24/04-----85.70%-----611-------------25

I have a query that requests a the user to enter a date and then returns that entire row of information. So, if the user enters 6/24/04, the query will correctly return 85.7, 611, and 25. For my reporting, I only want the user to enter a single date (as noted above). But I need the report to present a few additional elements.

1. I need to also report the previous week. So, if the user enters 6/24/04, I need the data for that week AND the prior week, 6/17/04.

2. I need to report a rolling 13 week average. So, if the user enters 6/24/04, I need the average of each column of data from the user entered date, back 13 weeks. So, the average of columns for 6/24, 6/17, 6/10, 6/3, 5/27, and so on until there are 13 weeks of data.

Thanks for the help!

 

[garyd1234]

write some code behind a button to put the date you want to see into a variable then use newvar= dateadd("d", -7, var) to go back 7 days at a time and requery the database