Query to provide total and breakdown counts (1 Viewer)

[hawg1]

Hi,

My table has a category field and a status field. Each category can have up to four statuses (open/closed/hold/resolved). I have a query that will provides a breakdown of the table by category and within each category, it lists the total count of each type of status.

My dilemma is that I would like to have a line at the end of each category that will count/sum up each status applicable to that category for a total count. I would then like to have a toal count at the end of the report.

I'm looking for something like this:

202 Request

Open 1
Closed 2
Hold 1
Resolved 1

Total 5

And so on.

To date I have been unsuccessfull in creating this code. All I can current get is everything above except the total line. Any and all help would be appreciated

Here is my query code

SELECT Calls.Category, Calls.Status, Count(Calls.Status) AS CountOfStatus, [Total Calls Query].CountOfID
FROM Calls, [Total Calls Query]
GROUP BY Calls.Category, Calls.Status, [Total Calls Query].CountOfID;

Thanks

 

[pbaldy]

You would typically do that in the report, not in the query. It can be done with a UNION query, but it's simplest to just use a Sum() function in the group footer of the report.