Need the group count to include zeros 2

[hemburyr]

Hi

I have 1 table to hold messages for various departments

ID Dept Message
1 Admin Message 1
2 Admin Message 2
3 Tech Message 3
4 Tech Message 4
5 Tech Message 5
6 Mgr Message 6
7 Mgr Message 7

I can get the following result :

Admin 2
Tech 3
Mgr 2

using the standard group and count to get the total number of messages per dept.

My question is how can I get a result like this

Admin 2
Tech 0
Mgr 0

if some of the records no longer exist or are flagged as a read message. The Dept types are fixed so part of the query can be - where Dept in ("Admin","Tech", "Mgr") so that other depts can be added if required.

Thanks in advance

 

[hemburyr]

Hi,

Sorry, I should of clarified my query a bit more.

The list of departments is generated from another query run against another database and the subsequent recordset returned is used to build the SQL statement to get the count of messages per department. I don't want to create a new table with a list of departments and neither do I want to use a linked table.

Sorry for the confusion.

 

[dhookom]

I don't want to create a new table with a list of departments" why?
You could probably use something ugly like
SELECT Count(*) as NumOf ,"Admin" as Dept
FROM tblMsgs
WHERE Dept = "Admin"
UNION ALL
SELECT Count(*), "Tech"
FROM tblMsgs
WHERE Dept="Tech"
...etc...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PHV]

if some of the records no longer exist or are flagged as a read message
Can't you add a WHERE clause ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244