Creating a Query to list all Months and Years from Jan 06 to Present date

[romit91]

Hi,

I'm trying to create a query or code that would generate a list of months from January 2006 to the present date. Can anyone help me out with this?

Example:

January 2006
February 2006
........
........
June 2011

Thanks a lot,

Romit.

 

[VilaRestal]

How about one table listing the twelve months, another table listing the years and a cross query joining the two. If you stored the month number as well as the name in the months table you could then filter it by monthyears not in the future

 

[VilaRestal]

Yeah, that's what I meant (I'm getting used to working in SQL server :s)

This is what it would look like (tables populated as you'd imagine):

 

[romit91]

Hi Boblarson and VilaRestal,

Thanks for your quick replies.

@Boblarson: I have another query that checks whether a tool has come in for repair or not according to its serial number. If the tool has come in for repair, it checks the manufacturing month of the tool and displays this data. However, the person who I'm writing this code for has an Excel file that he has been maintaining for about 2 years now. So, in essence he wants a list of all months (even if there is no tool that was manufactured in that month) so that he can copy the data from this query and paste it in his excel file.
I'm trying to create a query that would not need maintenance in the future. Is there a way to do this?

@VilaRestal: Thanks for sharing the query. However, do I manually have to enter the years in the tblYears table or will be updated automatically each year?

I really appreciate your help guys!!

Thanks a lot,

Romit.

 

[VilaRestal]

Just fill it for years up to 2100 (or more if you like)
The filter only shows years and months <= present

 

[romit91]

yup, that makes sense. haha!!

Thanks a lot!!

 

[romit91]

I tried your query and it works perfectly fine except that if you put a constraint on the 'Month' field, it will give you the Months until the current month in each year.

What I mean to say is, this is the output that I get:

Month_Year Expr1 January 2006 February 2006 March 2006 April 2006 May 2006 June 2006 January 2007 February 2007 March 2007 April 2007 May 2007 June 2007 January 2008
Anyways, I removed the constraint on the month field and it seemed to work fine. I don't mind the extra months displayed as well. (Just a feedback for anyone else trying to do the same thing.)

Thanks again,

Romit.

 

[VilaRestal]

No that's why the month constraint was on a separate line.

In SQL code the where clause is:

WHERE yearID < Year(Now()) Or (yearID = Year(Now()) And MonthNumber <= Month(Now()))

It does work I promise

 

[romit91]

Oops, I guess I messed up there. Yup, you were right, it works great!!