365 Day Avg Query

[TDS]

I have a table with two fields, Date and Sales. The data goes back 25 years.
I would like a query that gives me the 365 day avg for each month in the
last two years; i.e. The avg for May 06 thru May 07, then April 06 thru
April 07, and so forth. The final result would have 24 rows;

Date Expression 365 Day Sales Avg
04.06thru04.07Avg $61,500
05.06thru05.07Avg $73,650
06/06thru06.07Avg $65,300
Etc

Can I do this in one querie?

Terry ?

 

[John Spencer]

UNTESTED. But this might work if I've got my T1 and T2 relationship set
up correctly

SELECT Format(T2.DateField,"yyyy-mm"), Sum(T1.Amount)
FROM YourTable as T1 INNER JOIN YourTable as T2
ON T1.DateField > T2.DateField -365 and T1.DateField <= T2.Datefield
WHERE T1.DateField Between #2005/04/01# and #2007/04/30#
GROUP BY Format(T2.DateField,"yyyy-mm")



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================