Display "blank" rows in crosstab

Posted on 2012-08-20
Medium Priority
Last Modified: 2012-08-22
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.

Question by:emacyam
  • 2
  • 2

Assisted Solution

by:Evan Cutler
Evan Cutler earned 400 total points
ID: 38313749
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.
LVL 74

Accepted Solution

Jeffrey Coachman earned 1600 total points
ID: 38313788
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");
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38322201
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...

Expert Comment

by:Evan Cutler
ID: 38322221
Thanks :D


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

864 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