Cross Tab Query not recognizing '0' or null value

[Jim Dudley]

SQL: query 1

TRANSFORM Count(TmpAtt_DateRange.Type) AS CountOfType
SELECT TmpAtt_DateRange.WCode, TmpAtt_DateRange.Type, Count(TmpAtt_DateRange.Type) AS [Total Of Type]
FROM TmpAtt_DateRange
GROUP BY TmpAtt_DateRange.WCode, TmpAtt_DateRange.Type
PIVOT Switch([Credits]=1,"1 Credit",[Credits]=2,"2 Credits",[Credits]=3,"3 Credits",[Credits]>=4,"4 or more Credits");

SQL: query 2

TRANSFORM Count(TmpAtt_DateRange.Campus) AS CountOfCampus
SELECT TmpAtt_DateRange.SNUM, Count(TmpAtt_DateRange.SNum) AS [Total Of SNum], Sum(TmpAtt_DateRange.FGen) AS SumOfFGen
FROM TmpAtt_DateRange
GROUP BY TmpAtt_DateRange.SNUM
PIVOT TmpAtt_DateRange.Campus;

Issue:
Both of these queries work but dependent queries are breaking down due to a missing value or absence of data in the [2 Credits] field, also the [3 Credits] field and the [4 or more Credits] field. The reason for this that there is no records in the selected period that have 2 or more credits in the [Credits] field of the table.
In the case to the other query, there was no activity during the selected period at the [SY] Campus.
Subsequent queries which look for this data fail because there is no data.

Is it possible to modify the queries above so that they return a '0' or null value instead of ignoring that category. Conversly is it possible to have the subsequent queries not return an error, if there is not data for a specific field.

Sample subsequent query:

TRANSFORM Count(TmpAtt_DateRange.Campus) AS CountOfCampus
SELECT TmpAtt_DateRange.SNUM, Count(TmpAtt_DateRange.SNum) AS [Total Of SNum], Sum(TmpAtt_DateRange.FGen) AS SumOfFGen
FROM TmpAtt_DateRange
GROUP BY TmpAtt_DateRange.SNUM
PIVOT TmpAtt_DateRange.Campus;

Due to the condition described above, this query now returns the error:
'qx_pa_tmpRange_WS.[2 credits] does not recognize it as field name or expression.
Question:
Any suggestions as to adjust for this situation? I have several other queries that could fall into the same category were there no data in a given category of a Cross Tab Query.

Thank you.

Jim