Query to get month numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I there a way to get a sequence of number from a query. Some thing like:

Select 2004 as Year, (1 to 12) as Month
from [Whatever should be in here]

I need this to use as a base for my reports.

Thanks
Mauricio Silva
 
Create a table of numbers from 1 to 12. You can select the month based on
this table. You can also use the table to get the year.
A year in the past twelve years is:
Year(Date()) - SelectedNumberFromTable
A year in the next twelve years is:
Year(Date()) + SelectedNumberFromTable

You can use DateSerial to convert these numbers to an actual date value.
 
One POSSIBLE method

SELECT 2004 as YearNo, 1 as MonthNo
FROM SomeSmallTable
UNION
SELECT 2004, 2
FROM SomeSmallTable
UNION
SELECT 2004, 3
FROM SomeSmallTable
....

It would be better to build yourself a small table to hold this information, but
if you can't do that then the only way I can think of is to use a UNION query
similar to the above. You could also, do it by building the table in excel or a
text file and linking in the SQL statement to the table. Of course, you would
have to know reliably where the external document was located.
 
Back
Top