Hello All,
Im trying to create a crosstab query that shows a "0" value for rows not represented in the data. The design view of the query looks like this
The SQL is here:
TRANSFORM IIf(IsNull(Count([qQSTATS_master]![InOut]))=True,0,Count([qQSTATS_master]![InOut])) AS [Value]
SELECT tsupPTypeSTATS.PTID, tsupPTypeSTATS.PType
FROM tsupPTypeSTATS LEFT JOIN qQSTATS_master ON tsupPTypeSTATS.PType = qQSTATS_master.[Permit Type]
GROUP BY tsupPTypeSTATS.PTID, tsupPTypeSTATS.PType
PIVOT qQSTATS_master.InOut In ("In","Out");
And the datasheet result is here:
The trouble that I am having is that there is no data for the rows "LT", "LA", "MPGW" and LOC"; however, there is a "1" in the "In" column where I would like to have a "0". The "Out" column is calculated just fine.
Any ideas?