Statistics Reports with DCounts 1

[misscrf]

I 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.

Any help is greatly appreciated.

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would not use DCount()s for instance, you should be able to replace
=DCount("[CMatterNo]","tblCases"," CStatus= 'Open'")
with this expression in the attorney group header or footer
=Sum(Abs(CStatus= "Open"))
This will count the number of Open cases. You should also be able to Sum() or Count() other values.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

Thanks! I will try that.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

YES! Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

OK, I am finding an issue with this. The source query is showing the detail of all the records. I need to count the status or Open or Closed, only once for each matter number. It counts it for every record, which may be many per matter (if a matter has more than one EDD record or paper record). Can I have this count group the matters (despite the info in the query) and then count the Open or Closed?

Thank you!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

You can create running sum text boxes in group headers or footers to count unique values of a group field.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

I am not sure I understand. 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 better?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

The easiest solution might be to create totals queries by Attorney that you can join into your report's record source query.

The other alternative is to create an Attorney+Matter group header. In the Matter group header add a text box:
Name: txtCountOpen
Control Source: =Abs([Status]="Open")
Visible: No
Running Sum: Over Group (I think)
Then in your Att Group Footer, you can count the unique number of open matters with a text box:
Control Source: =txtCountOpen

Do the same for other stati.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

I think that did it! I then added them all up for a total matters count, like this:

=[txtStatusOpen]+[txtStatusClosed]+[txtStatusUnknown]+[txtStatusSettPend]

Thank you! I will have to run some checks but I think this got what I needed.

misscrf

It is never too late to become what you could have been ~ George Eliot