Crystal Reports query problem

I am writing a report that I cannot figure out. Here's what I'm working with.

Crystal Reports XI. I've got two tables involved.


Patient_Demo can have many Ext_Pat_IDS.

What I need is this. I can only show each patient once. IF they have an ID in Field1, use that, if not, use ID in Field2, etc. As it stands right now, I've jumped from having a result set of 2450 records to 4991 just by adding the ID to the report. I need it to remain at 2450, and only show the patient using the most primary ID.
Who is Participating?
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.

'IF they have an ID in Field1, use that, if not, use ID in Field2, etc. '

You'll have to explain this in more detail.  It sounds like a real problem on the face of it.
tmccrankAuthor Commented:
The patient can have up to 5 types of IDs. If there is an ID of Type "ULI", use that ID on the report, and move to the next patient. If there isn't an ID of Type "ULI", then check to see if there is an ID of Type "OOP". If there is, use that on the report and move to the next patient. IF there isn't, check for an ID of Type "HCN". If there is, use that ID on the report and move to the next patient.

Does that make sense?
How are you linking the tables?

Are you saying you need to show a patient if they have a ULI or OOP or HON but only once?

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

tmccrankAuthor Commented:
Crystal linked the tables for me automatically.

Yes, you're correct, I want to show the patient only one time, even if they have 3 IDs.
You told Y"es, you're correct, I want to show the patient only one time, even if they have 3 IDs."

I think .This is not a good idea.

if it it works then its ok.

You can create a new formula where in you can check
if field5="ULI" then
else if field6="OOPI" then
else if field7="Hon" then

and then you select expect and link the formula field to your main table which sorts patient info ( like

Rajendra Dewani
Jinesh KamdarCommented:
Try something like this Oracle pseudo-code.

SELECT Patient_Demo, NVL(Field1, NVL(Field2, NVL(Field3, NVL(Field4, Field5)))) Ext_Pat_IDS
Have you checked the tables links?  Are they correct?
Crystal automatic linking sometimes creates problems and uses fields it thinks should be linked?

You can group the report by patient then show the information in the group header.


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
tmccrankAuthor Commented:
THe table links are fine, they're bringing up the correct data. The problem is that one patient can have 5 IDs, and if they do, it shows that patient 5 times in the report, the only difference in each page being the ID.
Are you grouping on the ID field?

tmccrankAuthor Commented:
Sorry for the delay, I noticed that there WAS a problem in the linking, it wasn't clear until now.
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
DB Reporting Tools

From novice to tech pro — start learning today.