• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1306
  • Last Modified:

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."
0
WallyTee
Asked:
WallyTee
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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
 
Patrick MatthewsCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now