473,386 Members | 1,621 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

Grouped Statistical Report with a count of a subgroup under certain conditions???

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.
Nov 12 '07 #1
1 1907
12,516 Expert Mod 8TB
Why are you using DLookup and not a query to get your data?
Nov 13 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Justin Koivisto | last post by:
I am trying to create a report that displays a name of an advertising source and count of the number of times it was hit between certain date ranges. The data is split between two different...
by: VBSponge | last post by:
Hi all. In A2K i have a report with a control bound to to calculate the page count for the report. I need to retrieve this page count from the report, and build it into a TOC containing this an...
by: ZRexRider | last post by:
Hi, I have some relatively simple reports that pass parameters to SQL Server stored procedure to retrieve data. These reports are set to group the data by an individual's name and print their...
by: Sebastian | last post by:
Hello all I have a report where I have two nested groups. I know there are only three standard options for running sum: None, Over Group and Over All. I have a MyTextBox in detail section where...
by: kabradley | last post by:
Alrighty Guys and Gals, I have another question that I hope you all can help me with. I have a report that uses a cross-tab query as its record source. This cross-tab query is getting all of its...
by: jessicaeatworld | last post by:
Hi, I am using Access 2003 and need a report to show details and then a summary at the bottom that separates by market. I see that I could group, then put a summary after each group but I need the...
by: anubis2k7 | last post by:
Hi, I am having a problem with dynamically sorting/grouping data in my report at runtime. My problem is that when the report is run using sorting/grouping I am missing data. Specifically, my...
by: nospam | last post by:
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...
by: Doug | last post by:
What is the simplest way to make a report where only the records where a field matches a certain date are included, and the user first selects that date (from form or popup)? (I can write the SQL...
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.