| Author |
Topic |
|
bobross80
Starting Member
11 Posts |
 Posted - 2010-01-07 : 23:55:16
|
I'm trying to write a query that will produce:
January 2010
January 2009
January 2008
January 2007
February 2010
February 2009
February 2008
February 2007
March 2010
March 2009
March 2008
March 2007
April 2010
April 2009
April 2008
April 2007
.
.
.
n
This is what I have so far:
SELECT MonthName(Month([Program Attendance].Date)) AS TableMonth, Year([Program Attendance].Date) AS TableYear
FROM [Program Attendance]
GROUP BY MonthName(Month([Program Attendance].Date)), Year([Program Attendance].Date)
ORDER BY Year([Program Attendance].Date) DESC, MonthName(Month([Program Attendance].Date));
Its grouping by month and year, and ordering by year and month, but orders by month alphabetically. When I remove the monthname() from the order by clause and just use month(), I get an error.
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-08 : 01:22:19
|
What is the error message?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-08 : 02:38:26
|
SELECT MonthName(Month([Program Attendance].Date)) AS TableMonth, Year([Program Attendance].Date) AS TableYear
FROM [Program Attendance]
GROUP BY MonthName(Month([Program Attendance].Date)), Year([Program Attendance].Date),Month([Program Attendance].Date)
ORDER BY Year([Program Attendance].Date) DESC, Month([Program Attendance].Date);
Madhivanan
Failing to plan is Planning to fail |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-08 : 02:59:27
|
As OP stated this was what he has tried, hence we should no the error message...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-08 : 03:49:40
|
quote:
Originally posted by webfred
As OP stated this was what he has tried, hence we should no the error message...
No, you're never too old to Yak'n'Roll if you're too young to die.
Note that I included an extra expression in the GROUP BY Clause
Madhivanan
Failing to plan is Planning to fail |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-08 : 03:59:47
|
Aha!
I was blind. I think it is my age 
quote:
SELECT MonthName(Month([Program Attendance].Date)) AS TableMonth, Year([Program Attendance].Date) AS TableYear
FROM [Program Attendance]
GROUP BY MonthName(Month([Program Attendance].Date)), Year([Program Attendance].Date),Month([Program Attendance].Date)
ORDER BY Year([Program Attendance].Date) DESC, Month([Program Attendance].Date);
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
bobross80
Starting Member
11 Posts |
Posted - 2010-01-08 : 17:14:32
|
This is the output. So close! However, January 2007 should be grouped with January 2010 on the top...
Any ideas?
January 2010
February 2009
March 2008
January 2007
April 2007
May 2007 |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-08 : 18:11:44
|
Then change the order by and it will work.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
bobross80
Starting Member
11 Posts |
Posted - 2010-01-09 : 13:35:52
|
Thank you! Thank you! Thank you!
|
|
|
|
|
Month and Year Query