Grouped Report - Count Subgroup Where...

I have a report to count some statistics of case data. I can't figure
out how to make Access group and sum/count information to give me a
nice statistics report. Basically the database has case information
for matters at a law firm. The point is to create this report to
group on the attorney. Then for each attorney count the cases where
the status (CStatus is the field) is "Open", where the status is
"Closed", sum the size of the media in all of the attorney's cases,
count the paper items, count the media items, and sum the size of that
attorney's case repositories. Below is an example of the query data I
am dealing with:

It has each record for a case/attorney and every record of electronic
discovery, paper discovery, size, etc.

Attorney ID Matter Status AttorneyName Media Size
Repository Size Number of Paper Containers Media ID Meida
Size
1234 123456 Closed Jane Doe 1000 23456 1 123
5436
5678 123457 unknown John Smith 1001 23457 2
124 5437
9101 123458 Open Amy Clark 1002 23458 3 125
5438
1213 123459 unknown Mike Andrews 1003 23459 4
126 5439
9101 123460 test add case status Amy Clark 1004
23460 5 127 5440
1213 123461 Open Mike Andrews 1005 23461 6 128
5441
5678 123462 unknown John Smith 1006 23462 7
129 5442
5678 123463 unknown John Smith 1007 23463 8
130 5443
1213 123464 unknown Mike Andrews 1008 23464 9
131 5444
5678 123465 unknown John Smith 1009 23465 10
132 5445
1234 123466 unknown Jane Doe 1010 23466 11 133
5446

(Sorry if its messy)

So it My report groups on attorney. I have a sum of the repository
sizes for that attorney, a sum of the media size and a sum of the
paper containers.

Now I also want a count of the matters that have a status of open, the
amount that are closed, unknown, etc. The thing is that there might
be multiple records for one matter in this query, like say if there
are multiple media items or paper containers.

I need to count the "Open" case status records for each attorney
grouped by matter. I tried some dlookups to the table tblcases, and
to a query, but nothing seems to be working. I tried dcounts and
iif's.

Any thoughts?

Thank you.

Re: Grouped Report - Count Subgroup Where...

Until you got to the last paragraph I thought I understood your data
but here is something you can play with

1) Query with group by attornyID
2) Using the Query wizard:
CntClosed:iif([QueryName]![Status] = "Closed",1,0) as the field
and Sum as the query action.
CntOpen: Same as above but "Opened"
etc.
For the other fields that you want sums on just put the field and
have query action as Sum.


The conditions for the above to work:
-Only 1 record per attorney/Matter
-if above is not true then you need to create a lower level query that
groups by Matter so previous statement is true.
AND
-Status always the same for all records for a matter if there are
multiple records per matter.

Ron