select the last 12 months worth of data (1 Viewer)

[gwunta]

I need a query to select the last 12 months worth of data from a table "MAD". The user inputs a starting month and year and the query must then pull up the last 12 months of data. For example, I might want the 12 months of data from August 2001 to August 2002.

The data is stored daily with each record containing a month number (1-12), day number (1-7), date and year. This is the query I have been using so far.

SELECT DISTINCTROW tblYearNumbers.Year, tblMonthNumbers.Month, tblMonthNumbers.[Month Name], Sum(mad.[ENT 1]) AS [SumOfENT 1], Sum(mad.[ENT 2]) AS [SumOfENT 2], Sum(mad.[ENT 3]) AS [SumOfENT 3], Sum(mad.[ENT 4]) AS [SumOfENT 4], Sum(mad.[ENT 5]) AS [SumOfENT 5], Sum(mad.TOTAL) AS SumOfTOTAL
FROM (mad INNER JOIN tblYearNumbers ON mad.YEAR = tblYearNumbers.Year) INNER JOIN tblMonthNumbers ON mad.MONTH = tblMonthNumbers.Month
WHERE (((mad.YEAR) In ([Forms]![INPUT COMPARISON]![YearIn],[Forms]![INPUT COMPARISON]![YearIn2])))
GROUP BY tblYearNumbers.Year, tblMonthNumbers.Month, tblMonthNumbers.[Month Name];

I can get the query to list the correct years but it shows all of the months for each year.I wrote another query that could select the correct years but wouldn't select the appropriate months. This was because of the month numbers table listing the months in 1 to 12 so you couldn't just use a criteria that would list all months between 1 and 8 and years 2001 and 2002. e.g. the query would bring up August, September, October, November and December of 2001 and August, September, October, November and December of 2002.

Please help

 

[simongallop]

If the date that is from the form is the starting date of the year that you want then how about:

WHERE Mad.DateField>=[Forms]![INPUT COMPARISON]![DATE FIELD] And Mad.DateField < DateAdd("yyyy",1,Forms]![INPUT COMPARISON]![DATE FIELD])

HTH