Show where count = zero

[flester]

Have developed an Access dB for logging and reporting on support issues. The following query is used to show how many issues are raised in a specified time period.

SELECT ISSUES_BY_SYSTEM_AREA.report_category, Count(*) AS instances
FROM ISSUES_BY_SYSTEM_AREA
GROUP BY ISSUES_BY_SYSTEM_AREA.report_category;

REPORT_CATEGORY can have a number of values e.g.
Technical Support Issues
Configuration Changes
User Admin
Other

I have two questions
(1) How do I get it to report for each category even if the number of intances is zero? For example, if there have been no instances of 'User Admin' this month, I want the results to show as follows
(2) How to show the sum of these as well

For example what I want to be returned is:
Technical Support Issues 5
Configuration Changes 14
User Admin 0
Other 2
Total 21

Currently this is what would be returned:
Technical Support Issues 5
Configuration Changes 14
Other 2


Any Ideas?

 

[neileg]

I presume you have a table somewhere that contains all the possible values for REPORT_CATEGORY. Join this table with ISSUES_BY_SYSTEM_AREA in your query by using a left join. This will ensure that you get all of the categories whether or not there are corresponding records in ISSUES_BY_SYSTEM_AREA.