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.
1  1922  Rabbit 12,516
Recognized Expert Moderator MVP
Why are you using DLookup and not a query to get your data?
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 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 for a report that
does the following:
* prompt user for the date range
* get which sources were...
|
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 other reports.
All the reports are page-numbered consecutively in the order they are
printed (there are 7 reports in total, with about 60 pages in all).
I am having an issue with one of my reports returning a seemingly
incorrect page count,...
|
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 stored
procedure but the SP is designed to be "generic" - called from other
reports so I want...
|
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 a running sum over the
first group so when another second group begins MyTextBox value will
|
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 information from another query. That query, qryRpt, is pulling information from several different tables. One of these table is tblDistributions, a table that holds monthly distributions for a particular investment, and the second is tblDeduction, a...
| | |
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 Report to both queries?
Also I thought I could enter the markets in as Labels, then use DCOUNT to...
|
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 Incident, 1 Contact can have multiple Contact Incidents). This query is the dataset for my report.
I...
|
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 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...
|
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 to select
the records I want...)
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
