count based on another field

[barrynichols]

I have a table in my database which records when a case is marked by
an assessor.

The table shows the case number, the assessor ID that the case has
been assigned to and also an indicator to determine whether or not the
case is closed.

I need the query to show:

The assessor ID number
The number of cases assigned to a particular assessor
the number of cases assigned to a particular assessor that are
incomplete

The first two are fine, however, how do I limit the last part of the
query to show only the cases assigned to each assessor?

thanks

 

[Amy Blankenship]

I have a table in my database which records when a case is marked by
an assessor.

The table shows the case number, the assessor ID that the case has
been assigned to and also an indicator to determine whether or not the
case is closed.

I need the query to show:

The assessor ID number
The number of cases assigned to a particular assessor
the number of cases assigned to a particular assessor that are
incomplete

The first two are fine, however, how do I limit the last part of the
query to show only the cases assigned to each assessor?

I'd make a separate query that just returns that, then join the query to the
table. Or you can use a subquery.

HTH;

Amy

 

[Michel Walsh]

SELECT assessor,
COUNT(*) AS casesAssigned,
COUNT(indicator) As casesClosed,
COUNT(*) - COUNT(indicator) AS casesNotClosed
FROM tableName
GROUP BY assessor


I assumed the field indicator IS NULL when the case is still open, and not
null when the case is closed. If, instead, it is a Boolean value
(true/false), with the value true when closed, then, use:

SELECT assessor,
COUNT(*) AS casesAssigned,
-SUM(indicator) As casesClosed,
COUNT(*) - -SUM(indicator) AS casesNotClosed
FROM tableName
GROUP BY assessor


Hoping it may help,
Vanderghast, Access MVP