Crosstab Query sometimes loses fields

Hi Folks
Not sure if this should be in the Queries or Reports forum?

I have a MakeTable Query which selects data between 2 dates to create the following Table.

IncID, IncType, IncDate, AggType
15, Agressive, 23/02/2006, Verbal
23, Agressive, 14/05/2006, Threat
72, Agressive, 18/11/2006, Physical
89, Agressive, 20/12/2006, Verbal

I then have a crosstab query that counts the different "AggType" like this.
(This data is output to a report as both data and a chart)

Verbal, Physical, Threat
2, 1, 1

This all works great IF the date range is large enough that all 3 AggType fields are included. BUT, I my date range was from 01/05/2006 to 31/05/2006, I would only have 1 record (23, Agressive, 14/05/2006, Threat
) so the crosstab would result in ONE field only. (Threat)

The problem is that my report is looking for 3 fields (Verbal, physical and Threat)

How can I stop the report from giving an error if fields are missing from the crosstab result?

Thanks

In the SQL statement of the crosstab query, you can put an Nz(...)+0 in the Transform clause and an IN list of column heads after the Pivot clause like this:-

TRANSFORM Nz(Count(AggType))+0 AS CountOfAggType
SELECT IncType
FROM [TableName]
GROUP BY IncType
PIVOT AggType IN ("Verbal", "Physical", "Threat");
.

Thanks Jon
Thats the solution that I am looking for.