Crosstab, include all rows

[Tiro Cupidus]

I've searched old posts and used what I found, so please no "search for this" replies.

Here is the SQL for my crosstab:
<---
TRANSFORM IIf(IsNull(Count([qryTally].[Year])),0,Count([qryTally].[Year])) AS CountYear
SELECT tblMonths.Month, Count(qryTally.Year) AS Total
FROM tblMonths LEFT JOIN qryTally ON tblMonths.Month = qryTally.Month
WHERE qryTally.Year=fInputBox("FY")
GROUP BY tblMonths.Month, tblMonths.MonthID
ORDER BY tblMonths.MonthID
PIVOT qryTally.Type In ("FR","IAR","EAD");
--->
I have created a table with all 12 months listed in it, attempting to force the query to display data for each month, zeroes if there is no data. Right now it displays data only for October and November. Let me know if you need the SQL from qryTally.

Thanks for your help.

 

[dcx693]

Check out the ColumnHeadings property for crosstab queries. I think you need to specify the columns you want in order to get them to show up if they have no data.

 

[Nouba]

I just set up a table instead of your query, also I didn't use a criteria for the year. With only one record in qryTally I got the following result

Month	Total	FR	IAR	EAD
January	1	1	0	0
February	0	0	0	0
March	0	0	0	0
April	0	0	0	0
May	0	0	0	0
June	0	0	0	0
July	0	0	0	0
August	0	0	0	0
September	0	0	0	0
October	0	0	0	0
November	0	0	0	0
December	0	0	0	0

Here's the query I used

TRANSFORM CInt(Nz(Count(Q.Year),0)) AS CountYear
SELECT
  M.Month
, Count(Q.Year) AS Total
FROM tblMonths AS M
  LEFT JOIN qryTally AS Q ON
    M.Month = Q.Month
GROUP BY
  M.Month
, M.MonthID
ORDER BY M.MonthID
PIVOT Q.Type In ("FR","IAR","EAD");

 

[Tiro Cupidus]

Well, Nouba, I took the criteria for the year out of the crosstab, then added the criteria to qryTally. It works fine now. =) I wonder why it makes a difference...