How do I make months appear to be like 1-12 (first year of my database) and so on?

[michelle copper]

How do I create a query for a new column for my months to appear like 1-12 means the first year of my database (eg. 2008) and 13 to 24 means (2009) and 2536 (2010) and so on? Is there a way? so it is like 13 means january of 2009 and 14 means february of 2009.

[Rabbit]

Take the year of the date, subtract 2008, multiply by 12, then add the month number of the date.

[michelle copper]

:S
sorry Rabbit..I did not get what you mean.
Could you explain? How do I write that in the query?

[NeoPa]

Something like :

SELECT (Year([YourDate]) - 2008) * 12 + Month([YourDate]) AS [MonthID]

[michelle copper]

I do not have DATE in my table actually. I should have mentioned that before
I have

ID
YEAR
MONTH
QUARTER
TIME
HOME

[ADezii]

You may also want to make sure that there are no NULL Values in the Date Field, and that it is >=#1/1/2008#.

1. Sample Data (tblTest)
   

   TestDate
   3 /17/2008
   12/31/2009
   11/30/2009
   3 /3 /2009
   1 /1 /2010
   8 /23/2010
   4 /29/2010
   7 /9 /2010
   12/25/2010
   7 /7 /2008
   3 /3 /2011
   10/10/2012
   6 /16/2012
   5 /5 /2008
   5 /23/2013
   4 /30/2219
   9 /1 /2014
   2 /27/2007
   12/31/2007
   6 /25/2543

2. SQL Statement:
   

   SELECT tblTest.TestDate, Month([TestDate])+((Year([TestDate])-2008)*12) AS MCode
   FROM tblTest
   WHERE (((tblTest.TestDate)>=#1/1/2008# And (tblTest.TestDate) Is Not Null));

3. Results:
   

   TestDate	      MCode
   3 /17/2008	      3
   12/31/2009	     24
   11/30/2009	     23
   3 /3 /2009	     15
   1 /1 /2010	     25
   8 /23/2010	     32
   4 /29/2010	     28
   7 /9 /2010	     31
   12/25/2010	     36
   7 /7 /2008	      7
   3 /3 /2011	     39
   10/10/2012	     58
   6 /16/2012	     54
   5 /5 /2008	      5
   5 /23/2013	     65
   4 /30/2219	   2536
   9 /1 /2014	     81
   6 /25/2543	   6426

[ADezii]

Add any additional Fields as needed:

SELECT tblTest.Month, tblTest.Year, ([Month]+([Year]-2008)*12) AS MCode
FROM tblTest
WHERE (((tblTest.Year)>=2008));

[NeoPa]

Michelle:
I should have mentioned that before

Yup. That would make sense ;-)

Actually it makes it somewhat easier :

SELECT ([YEAR] - 2008) * 12 + [MONTH]) AS [MonthID]

[Rabbit]

The algorithm still stands. In place of the year and month functions, you use your year and month fields.

[michelle copper]

Thank you Rabbit and NeoPa again! It works! :)

[michelle copper]

and Thanks ADezii!! You three have helped me a lot!! :DD