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
04 hfesting


Table: Issues
Fields Issueno, CaseNo, Closed
data...
IssueNo CaseNo Closed
01 01 False (manager = jsmith)
02 01 False (manager = jsmith)
03 01 True (manager = jsmith)
04 02 False (manager = jsmith)
05 03 False (manager = lgooden)

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 3
lgoodens 1
hfesting 0

Can someone give me the sql to do this?

Please note that I also want to make sure that managers that do not have any
related issues at all (as in teh case of hfesting) I want to make sure it
still shows "0" for them...

Thanks,

Brad

 

[Marshall Barton]

Check your earlier post

 

[Jim Evans]

Create the query with the query designer and then look at the SQL view.

Jim Evans

 

[RipperT]

Brad, try this:

SELECT CustServ.Manager, COUNT(Issues.CaseNo)
FROM CustServ INNER JOIN Issues ON CustServ.CaseNo = Issues.CaseNo
WHERE Issues.Closed = "False"
GROUP BY CustServ.Manager

This assumes that "false" is a string value and not a check box. If its a
check box, I don't know how to reference it in a query. You'll have to look
up it's data type in Access help and go from there.
I don't understand the last paragraph about related issues. I'm trying, but
I just can't figure out what "hfesting" is. Can you clarify?

Ripper

 

[Brad Pears]

Thank you, yes true/false is an access field of type "yes/no". YOu can just
use = true or = false (whatever it happens to be) without quotes and the SQL
works fine. example... "select * from table where somefield = true;"

The "hfesting" you were wondering about was the manager's username. This
particular manager had customer issue cases but had no issues associated. I
want the query to include him in the list but to show a 0 in the number of
open cases column.

Thanks,

Brad

 

[tina]

getting Access to tell you all about what is "there" is pretty easy -
getting it to tell you about what is "not there" is harder (at least for
me!).
here's a solution that i think will work for you, though it's clunky.

qryOpenIssues:
SELECT CustServ.Manager, Count(Issues.Closed) AS CountOfClosed
FROM CustServ INNER JOIN Issues ON CustServ.CaseNo = Issues.CaseNo
WHERE Issues.Closed=False
GROUP BY CustServ.Manager;

qryManagers:
SELECT Manager
FROM tblCustServ
GROUP BY Manager;

qryManagerIssueCount:
SELECT qryManagers.Manager, Nz([CountOfClosed],0) AS NoOpen
FROM qryManagers LEFT JOIN qryOpenIssues ON qryManagers.Manager =
qryOpenIssues.Manager;

correct any of the table/field names i may have gotten wrong in the SQL.
note: do you have a supporting (lookup) table listing all the managers? if
so, are you using the primary key values from that table in the Manager
field in table CustServ? if yes, then you don't need qryManagers above; just
use the managers table itself in qryManagerIssueCount.

hth