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