13
have a report that I am trying to build and 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:
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 it is messy.
My issue is that I have a dcount for the open/closed/unknown cases, but it doesn't seem to work.
This is the formula: =DCount("[CMatterNo]","tblCases"," CStatus= 'Open'")
Am I missing something? It gives me the same total for every attorney. I have a report group header for attorney, and all of the fields are put there.
I have tried Dcounts, Dlookups, iifs and anything else I can think of.
The problem here, is that I need to group my count within the attorneys grouping on the report. If I make a matter group, I will get a repeating set of counts for each matter. Not what I want.
Let's say my query has this (Abrreviating the columns):
Att Matter Status Media
John Doe 123456 Open 4325
John Doe 123456 Open 123
John Doe 456789 Unknown 0
John Doe 234687 Closed 7894
John Doe 234687 Closed 4654
John Doe 124578 Closed 32467
John Doe 124578 Closed 13214
I need to see the following on the report for John Doe's Statistics:
Open Cases: 1
Unknown Cases: 1
Closed Cases: 2
If you look at just the matter and status, 1 matter's status should only count once.
Does this explain my dilema?
Any help would be wonderful.
