chart series options

[lscheer]

I am putting charts into a report in Access that draw data from a query. the query is a summary query that counts the total instances of each response, and the response value is used as the series for the x-axis in the chart, with the bar showing how many respondants their were for each one. What I would like to do and can't figure out how, is to display even those response categories that don't get any hits (i.e. their "count" would be zero).

Any ideas?

 

[liddlem]

Hi Ischeer
I had the same problem (see thread http://www.access-programmers.co.uk/forums/showthread.php?t=139086) and Jon K answered with the following which worked for me.

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