Report on a single record when there are multiples

I need to create a report that reports on a single client per case and calculates unduplicated counts of clients served.

The simplest of these reports is for gender. If there are two (or more) clients in the case, I need to report the gender of one of them. And if the client I report on has more than one case, I need to count that client only once in the report.

My first attempt was to do a distinct count of ClientID grouped by gender. That gets the unduplicated count but it counts every ClientID in a case, so if there's a male and a female client in the case, they get counted once as a male and again as a female (or as two females if there are two female clients, etc.). I have to count one or the other, not both.

I've attached a file that has screen shots in it of the report design, the Database Expert links, and the output.

I've tried all sorts of groupings including ClientID (the field is NameCardID), CaseID (the field is FileID), @Gender (a formula that returns "Male", "Female", or "Unknown"), and various combinations of the three.

Where do I go from here?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

WHat data do you have?

Is it 1 record per case or do you have joined tables so you get multiple records per case if a case has multiple clients?

If Joe has a case and Joe and Tom have a case is that counted as 1 or 2?

I don't think this can be done with just grouping.

chenegarAuthor Commented:
I have joined tables so I get multiple records per case if a case has multiple clients.

There's a case table and a clients table and a third table that assigns clients to cases.

If Joe has a case and Tom & Joe have a case, that's 2 cases but it should be counted as only 1 client.
chenegarAuthor Commented:
Given all my experimentation, I'm confident it can't be done with grouping!

Does that mean I need help with setting up formulas and passing values? I've figured out how to get a single ClientID to show up in the report (put ClientID in the Case group footer) but I can't figure out what to do next.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

The problem is you need to track the clients who have been counted

Consider this
Case 1      Joe
Case 2      Joe Tom Harry
Case 3      Tom Harry

Is that 1 or 2?

Are you just trying to get a summary or are you trying to show some details?

chenegarAuthor Commented:
That's 2 clients. Cases 1 & 2 have 1 client. Case 3 has 1 client.

I'm trying to get a summary: An unduplicated count of all clients that are male, female, or unknown.

And it gets more complicated. In another report I need to report on income data that's stored only at the case level but needs to be reported for unduplicated clients.

The Joe Tom Harry case makes me realize just how complicated this is going to get. That kind of case doesn't occur very often.

The most common kind of cases with multiple clients is:

Case 1    Joe
Case 2    Joe Tom

This is 1 client.

Less often is this type:

Case 1   Joe
Case 2   Joe Tom
Case 3   Tom

That's 2 clients.

Perhaps I should consider a SQL view that gives me one client per case?
I have trouble understanding why TOM from case 2 doesn't count but TOM from case 3 does.

How would you choose the one client per case


Case 1   Joe Pete
Case 2   Joe Tom
Case 3   Tom Pete

Is that 2 or 3?
If you choose Pete, Joe, Tom it would be 3
but if you choose  Joe, now case 2 doesn't count, then choose either Tom or Pete gives 2

Case 1   Pete Joe
Case 2   Joe Tom
Case 3   Tom Pete
Is that any different?

Is there an indication of which is the primary client?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Going back to your original post:

 > That gets the unduplicated count but it counts every ClientID in a case, so if
 > there's a male and a female client in the case, they get counted once as a male
 > and again as a female (or as two females if there are two female clients, etc.).
 > I have to count one or the other, not both.

 So, if you have something like:

Case 1  Pete Sarah

 That will count as one male, or one female, client (one or the other)?  What's the point of a gender count when you are arbitrarily not counting all of the genders?  It's like if you wanted a count of the clients that were over 50, but when there were multiple people associated with a case, you randomly picked one and checked their age, and ignored the rest.  What could that really tell you?

 Maybe it makes sense to you, but I'm really not seeing it.  The better we understand what you're trying to do, the more likely it is that we'll be able to help.

chenegarAuthor Commented:
It only makes sense to me because that's what I'm required to do. It's a report for a government agency and the directions state very clearly that we are to report only one client per case and it is to be an unduplicated count.

I get that they're trying to find out how many people were assisted, which is where the unduplicated count comes from, but I don't see why they don't want to know the full count.

As an aside, I created a report for another government agency that required the same thing and required that client data not change (like household size and income, both of which change with some regularity in our client population). For that report I pull data from the production database then put it into an Access database to "freeze" it. Whenever I have to report on that client, I use the original "frozen" data regardless of whether it has changed.

So in Michael's first example . . .

Case 1   Joe Pete
Case 2   Joe Tom
Case 3   Tom Pete

. . . there would be two clients counted, not three. That's because there can only be one client per case. If I count Joe in Cases 1 & 2, I can count only Tom or Pete in Case 3.

In the second example, there would also be only two clients:

Case 1   Pete Joe
Case 2   Joe Tom
Case 3   Tom Pete

If I count Pete in Case 1, I also have to count him in Case 3. Then I'd count either Joe or Tom in Case 2.

Remember, I'm not counting cases, I'm counting clients but I can only have one client per case. I count cases in a different report that is straightforward.

Since there is no identification of a primary client, I've decided that the client who is created first (and has a "lower" ID) will be the primary client. That person is usually the one who called for help. The second client gets added later as details emerge. I know there are situations when I'll count a client that I shouldn't, but I can't think of another way to decide on the primary client.

Here's an example: A person calls for help with a foreclosure. A case is created for him and while getting details on the case the advocate learns that his wife is also on the mortgage. Now we have two clients and one case. As far as I'm concerned, two people have been helped. But for this report, I'll report only on the husband because his record got created first.

If the wife then calls for help on another problem and her husband is also a client, the husband will be counted as the primary client because his existing record was created first.

I know it doesn't make sense.
chenegarAuthor Commented:
Although we didn't come up with a solution in Crystal Reports, your questions really helped me to think through what I needed to do. My last post made me realize I needed a SQL view to solve this problem.

I created a view that selects the "minimum" ClientID, which gives me one client per case. I plugged that view into my Crystal Report and now I'm getting the one client per case that I need.

It's crazy that I have to do this, but "[mine] is not to reason why."
FWIW, this does seem like the kind of thing that might be better handled by manipulating the data before it gets to the report, so doing it in SQL might be the best solution.

 I still don't really get it.  It just seems so arbitrary.  But as long as you're getting what you need.  :-)

chenegarAuthor Commented:
Thanks, James.

I agree that Crystal Reports wasn't the right tool, but I had to work through it before I realized that.

It doesn't seem arbitrary, it is arbitrary. I think it may actually be a holdover from the days when case management systems wouldn't let you have more than one client. Still . . .
You gotta do what you gotta do.  :-)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.