MS Access Crosstab Query -- column headings that display year and month across multiple years

I'd like to create a multi-year crosstab query that looks like this:

                                     2008 Nov     2008 Dec     2009 Jan    2009 Feb
            Group 1
            Group 2
            Group 3

I know how to do this for "quarters" (e.g., 2008 Q2), but I can't seem to find a way to do it for "months."
WallyTeeAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
No points for this, please :)

WallyTee said:
>>I'd love to get them in chronological order.  Any ideas on this?

PIVOT Format(a.RegisteredDate,"yyyy-mm")
0
 
puppydogbuddyCommented:
Continuing from the previous question you submitted, try this:
          PIVOT Format(a.RegisteredDate,"yyyymmm");
0
 
WallyTeeAuthor Commented:
Hi puppydogbuddy:   Thanks for the super-quick response!  

The Years and Months appeared OK, but they are in alphabetical and not chronological order, left to right.  I'd love to get them in chronological order.  Any ideas on this?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
puppydogbuddyCommented:
Try this-change the two lines as indicated:
Year(Format(a.RegisteredDate, "yyyymmm")>2005      '<<<<<<<<
GROUP BY a.Product
PIVOT (a.RegisteredDate);                   '<<<<<<<<<<
0
 
puppydogbuddyCommented:
oops, left out a right parens:

Year(Format(a.RegisteredDate, "yyyymmm"))>2005      
0
 
GRayLCommented:
Pivot Year(Format(a.RegisteredDate" "yyyy mmm") in ("Jan","Feb","Mar",.....,"Nov","Dec")
0
 
GRayLCommented:
Correction:

Pivot Format(a.RegisteredDate, "yyyy mmm") in ("Jan","Feb","Mar",.....,"Nov","Dec")
0
 
GRayLCommented:
If you can accept 20008 11, 2008 12, 2009 01, etc>

PIVOT Format(a.RegisteredDate,"yyyy mm")


0
 
WallyTeeAuthor Commented:
Good solution. It uses numbers to represent months, but that's fine.  They appear in the correct order.  Many thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.