Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crosstab query include 'null rows'

Status
Not open for further replies.

BrianWen

Programmer
Jun 8, 2009
102
DK
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).
 
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.
 
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.
 
Nevermind, I understand what you mean...
It worked just fine. Thanks for the help!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top