Display "blank" rows in crosstab

Posted on 2012-08-20
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
    LVL 9

    Assisted Solution

    by:Evan Cutler
    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

    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
    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...
    LVL 9

    Expert Comment

    by:Evan Cutler
    Thanks :D


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now