Displaying zeros in a report?

[mom2quinn]

I haven't used MS ACCESS in a very long time and have been asked to
create some statistical reports. I'm working with an issues database
that has a status and priority for each issue. Statuses (Open,
Closed, Assigned, re-test) and Priorities 1. Critical, 2. High, 3.
Medium, 4. Low.

This is how I would like the report to look

Open 12 Critical 0
High 1
Medium 7
Low 4

Assigned 10 Critical 0
High 0
Medium 4
Low 6

So i've got the layout and everything but the report does NOT display
the priority or the count when it is zero.

If anyone could guide me on where and how to make it so that this will
display as above it would be greatly appreciated.

Regards,
Tania

 

[Duane Hookom]

Apparently you need to generate records where there are no records. If this
is the case, you can generally use a cartesian query like:

== qcarPriSta ===
SELECT Priority, Status
FROM tblPriorities, tblStatus

This query can be joined into your report's record source to display all the
records from qcarPriSta.

 

[KARL DEWEY]

Try these two queries --
qryPriorityStatus --
SELECT tblIssues.Priority, tblIssues.Status
FROM tblIssues
GROUP BY tblIssuesPriority, tblIssues.Status;

SELECT [qryPriorityStatus].[Priority], Count([tblIssues].[Priority]) AS
[PriorityCount], [qryPriorityStatus].[Status], Count([tblIssues].[Status]) AS
[StatusCount]
FROM [qryPriorityStatus] LEFT JOIN [tblIssues] ON
([qryPriorityStatus].[Priority] = [tblIssues].[Priority]) AND
[qryPriorityStatus].[Status] = [tblIssues].[Status])
GROUP BY [qryPriorityStatus].[Priority], [qryPriorityStatus].[Status];