Here's where I stand. I have two queries. The first calculates a 7-day moving average (Avg7Day) for every day. Here's how it works:
SELECT T1.SITE_NO, T1.DATE, T1.FLOW, Avg(T2.FLOW) AS Avg7Day, Year(([T1].DATE)) AS FY
FROM [USGS RAW] AS T1 INNER JOIN [USGS RAW] AS T2 ON T1.SITE_NO = T2.SITE_NO
WHERE (((T1.SITE_NO)="03345000") AND ((T1.DATE)>=([T2].[DATE]-3) And (T1.DATE)<=([T2].[DATE]+3)))
GROUP BY T1.SITE_NO, T1.DATE, T1.FLOW, Year([T1].DATE);
The next query is supposed to calculate a minimum the minimum 7-day average over the following year, for each day. For example, for 1/1/2001, it calculates the minimum 7-day average between 1/1/2001 and 12/31/2002. For 1/2/2001, it calculates the minimum 7-day average between 1/2/2001 and 1/1/2002. The problem is that it is giving me the 7-day average corresponding to the exact day. There is no "minimum over a year" being calculated. Here's how it's written:
SELECT [T3].[SITE_NO], [T3].[DATE], Min(T4.Avg7Day) AS AnnualMinimum
FROM [7_Day_Avg] AS T3 INNER JOIN [7_Day_Avg] AS T4 ON ([T3].[DATE] = [T4].[DATE]) AND (T3.SITE_NO = T4.SITE_NO)
WHERE (([T3].[DATE]>=[T4].[DATE]) And ([T3].[DATE]<=DateAdd("yyyy",1,[T4].[DATE])))
GROUP BY [T3].[SITE_NO], [T3].[DATE];
Then I need to sort it on 10/1/XXXX, which will give me the minimum 7-day average for 365 days beginning with 10/1 of each year. I'm all out of ideas at this point.