problems with group by query...

[Brad Pears]

I am working on an an Access 2000 query to list the number of active cases
for each manager and am having some problems....

Here is a simplified version of the tables and some data...

Table: CustServ
Fields CaseNo, Manager
data...
CaseNo Manager
01 jsmith
02 jsmith
03 lgooden

Table: Issues
Fields Issueno, CaseNo, Closed
data...
IssueNo CaseNo Closed
01 01 False
02 01 False
03 01 True
04 02 False

What I want to list is a group by query by manager giving me just the
individual manager names and the sum of the total number of open issues
(closed = false) for each.

So using the above tables and data, I would want my query to generate data
as follows...

Manager Open Cases
jsmith 2
lgoodens 1

Can someone give me the sql to do this?

 

[Marshall Barton]

I am working on an an Access 2000 query to list the number of active cases
for each manager and am having some problems....

Here is a simplified version of the tables and some data...

Table: CustServ
Fields CaseNo, Manager
data...
CaseNo Manager
01 jsmith
02 jsmith
03 lgooden

Table: Issues
Fields Issueno, CaseNo, Closed
data...
IssueNo CaseNo Closed
01 01 False
02 01 False
03 01 True
04 02 False

What I want to list is a group by query by manager giving me just the
individual manager names and the sum of the total number of open issues
(closed = false) for each.

So using the above tables and data, I would want my query to generate data
as follows...

Manager Open Cases
jsmith 2
lgoodens 1

Try playing around with this:

SELECT CustServ.Manager, Count(*) As OpenCases
FROM CustServ LEFT JOIN Issues
ON CustServ.CaseNo = Issues.CaseNo
WHERE Issues.Closed = False
GROUP BY CustServ.Manager