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

 

[gwunta]

Can anyone help???

Is there anyone out there who has any thoughts on this, even can suggest making a new query. I thought choosing a range like this wasn't that difficult.

Cheers

 

[geralf]

Hi there 'down under'

Is there some specific reason why you keep the date, month and year numbers separated instead of using a Date format?

I've made a query which pulls up the 12 months after a input of a month/year number. (0201 = february 2001).

If you had used a Date field you could have used the DateDiff Function directly to return number of months between two dates.

First the MonthYear Input has to be coverted to a valid Date Format in the query before using the DateDiff Function. After re-reading your post I see that the 'date' stored in the records are day/month and year numbers?. If so, you'll have to covert this also to a valid Date format. Since you only will be entering the MonthYear number - we'll add '01.' to the MonthYear Input, so it gets to be a valid date.

Here's the SQL view of the query:

SELECT [StartMonthYear] AS MonthYearInput, tblTest.DateField, DateDiff("m","01." & Left([StartMonthYear],2) & "." & Right([StartMonthYear],2),[DateField]) AS MonthsBetween
FROM tblTest
WHERE (((DateDiff("m","01." & Left([StartMonthYear],2) & "." & Right([StartMonthYear],2),[DateField]))>=0 And (DateDiff("m","01." & Left([StartMonthYear],2) & "." & Right([StartMonthYear],2),[DateField]))<=12));

Change to your names for controls and tables.

This query ONLY pulls records from the 12 months after the start date.

Post back if you have any problems.

Cheers
Gerhard