473,585 Members | 2,536 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.

Nov 12 '07 #1
1 3037
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

Nov 13 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2718
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 databases: this access db, and a remote MySQL server. The MySQL tables are linked in the access db. What I have done so far is created an On Open event...
1
2352
by: Melissa | last post by:
Can anyone help me with this --- I have a grouped report with a subreport in the group footer and the report starts a new page for each group. The subreport is based on a query which has criteria in one of the fields. I have a function in a standard module where the value of the function is part of the criteria. For each group, I need to...
6
3312
by: Mike Conklin | last post by:
This one really has me going. Probably something silly. I'm using dcount for a report to determine the number of different types of tests proctored in a semester. My report is based on a parameter query which is the recordsource for the report. The parameter is <=. The query returns the correct amounts upto the date entered (no need for...
1
1703
by: Randy | last post by:
I have trying to figure out the above topic. Can someone help me with this? I need for staff to be able to go to a dialog box or a form and then be able to enter data in mutliple fields and then pull up a report. I have tried parameter queries but it does not seem to work in a grouped report. I put text boxes on the report but that did...
0
1457
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 name along with a count field. I would like to sort these reports by descending value of the count field. (normally I would do all of this in the...
3
4102
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 the data is summed over group. But the data is summed over the second group. When a new group begins MyTextBox value is resetting to zero. I need...
1
1781
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 details on the top and the summary at the bottom. I have 2 queries- one for the details and one for the summary. If there was someway to link the...
1
4138
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 situation is that I have a query that contains results from 2 tables that have a one to many relationship to each other (ex. 1=Contact, M=Contact...
1
1920
misscrf
by: misscrf | last post by:
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...
0
7904
marktang
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8195
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7947
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8209
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6596
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3831
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3856
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2340
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1171
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.