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

Display "blank" rows in crosstab

I have a crosstab query and would need to display all 12 months (rows).  However, if some of the months are returning "blank/null" then the months are not being displayed.  Is there a way to return all the months?

Currently, my query is only displaying March, May, September, October and November.

  • 2
  • 2
2 Solutions
Evan CutlerVolunteer Chief Information OfficerCommented:
To do that, you need to reverse the query.

You need to Select ALL of the months, then LEFT JOIN against a select statement (not a direct table join) asking for two columns:  month and data;

When I say Select ALL of the months, I mean use a reference table.
Jeffrey CoachmanMIS LiasonCommented:
I believe the crosstab query wizard does this for you by default.

Here is an example of how it hardcodes the months:

TRANSFORM Sum(Orders.[Freight]) AS SumOfFreight
SELECT Orders.[EmployeeID], Sum(Orders.[Freight]) AS [Total Of Freight]
FROM Orders
GROUP BY Orders.[EmployeeID]
PIVOT Format([OrderDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Jeffrey CoachmanMIS LiasonCommented:
Note that arcee123's "Reference table" suggestion is what I would use if the the wizard was not available.
That approach also provides for more flexibility...
Evan CutlerVolunteer Chief Information OfficerCommented:
Thanks :D

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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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