How to write a query to return the average of three number (from the max value)

[forestpool]

Hi all, I have a table looks like this

Date	ValueX
Jan 14	12
Feb 14	18
Mar 14	5
Apr 14	4
May 14	10
Jun 14	5
Jul 14	16

I would like a query that returns the AVERAGE figure of the three values starting from the maximum value



In this case, it is (18+5+4) / 3 = 9

Thanks

[June7]

Won't be simple.

Need to pull TOP 3 date records starting with the max ValueX record. This requires nested subquery http://allenbrowne.com/subquery-01.html or domain aggregates.

SELECT TOP 3 * FROM tablename WHERE [Date] >= DLookup("[Date]", "tablename", "ValueX=" & DMax("ValueX", "tablename")) ORDER BY [Date];

This assumes the dates are unique.

Now use that query in another query to average the 3 records.

SELECT Avg([ValueX]) AS AvgVal FROM query1;

Date is a reserved word. Should not use reserved words as names.