Crosstab query include 'null rows'

[BrianWen]

Hi all!

I have a table with some amounts, a date and a region. I'm building a crosstab query to display a the date (consolidated into quarters) as row headings (so four rows Q1, Q2, Q3 and Q4) and region as column heading and of course the amount as value.

That works perfectly, only I want to add a Where clause on the date, so that I can look at q1-q4 of a single year. Here's the problem: Some years they may not be any records in a specific quarter, so I end up having for instance only rows for Q2 and Q4. I'm using the column headings functionality, but is there a cool way to make sure, it will also show Q1 and Q3 with just blanks or zeroes? - I tried a UNION, but that won't work in a crosstab (as expected).

 

[BrianWen]

I was thinking the solution perhaps could in adding date another time as a row heading, but I can't figure out how that could be done to get all four quarters.

 

[dhookom]

Create a table with records for Q1 to Q4. Then create a final query of this table LEFT JOIN to the crosstab query.

Duane
Hook'D on Access
MS Access MVP

 

[BrianWen]

Sounds like something I could use, but I'm not entirely getting what you're telling to do. Could you elaborate just a bit. I know my way around SQL, so I do know LEFT JOIN for instance.

 

[BrianWen]

Nevermind, I understand what you mean...
It worked just fine. Thanks for the help!!