Date Calculation in Query, Previous Week Calculations

[elephant97]

Let me start off with the data set first, all in one table:

Name--------Date----Metric_1------Metric_2
John--------7/1/04-----11------------21
Tom--------7/8/04-----88------------99
John--------7/8/04-----103----------104

Here are my needs for a query:
1) When a name is selected, report only the 2 most recent metrics for that name in separate fields
2) When a name is selected, compile the previous 13 entries of the metric and take an average. So, under metric_1 there would be an avg of the last 13 weeks. Under metric_2, an average of the last 13 weeks, etc.

Hope that isn't too complicated. Thanks for the help!!!

 

[bat17]

1)
Filter the query by the name you wish to report, you can use the builder to take criteria from a text box on a form which is useful for this sort of thing.
Sort the query by the date field in descending order.
In the Queries property sheet set the Top Values to 2.

2)
I would do this in 2 steps, first create and save a query the same as above but with the top values set to 13

create a new query based on the above query but leaving out the dates. Turn this into a Totals Query (Button on toolbar)
then in the Totals row select Avg under your metrics.

HTH

Peter