Solved

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

Posted on 2009-04-13
9
1,276 Views
Last Modified: 2012-06-27
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
Comment
Question by:WallyTee
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24129799
Continuing from the previous question you submitted, try this:
          PIVOT Format(a.RegisteredDate,"yyyymmm");
0
 

Author Comment

by:WallyTee
ID: 24129869
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24129948
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24130013
Try this-change the two lines as indicated:
Year(Format(a.RegisteredDate, "yyyymmm")>2005      '<<<<<<<<
GROUP BY a.Product
PIVOT (a.RegisteredDate);                   '<<<<<<<<<<
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24130020
oops, left out a right parens:

Year(Format(a.RegisteredDate, "yyyymmm"))>2005      
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24130057
Pivot Year(Format(a.RegisteredDate" "yyyy mmm") in ("Jan","Feb","Mar",.....,"Nov","Dec")
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24130061
Correction:

Pivot Format(a.RegisteredDate, "yyyy mmm") in ("Jan","Feb","Mar",.....,"Nov","Dec")
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24130083
If you can accept 20008 11, 2008 12, 2009 01, etc>

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


0
 

Author Closing Comment

by:WallyTee
ID: 31569497
Good solution. It uses numbers to represent months, but that's fine.  They appear in the correct order.  Many thanks.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can you open the FORM2 2 32
Access Changing Number to Date with Seperator 5 21
SQL Error - Query 6 26
Combo Box with a control source is "locked" 10 9
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question