tmccrank
asked on
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
Ext_Pat_IDS
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.
Crystal Reports XI. I've got two tables involved.
Patient_Demo
Ext_Pat_IDS
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.
ASKER
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?
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?
mlmcc
Are you saying you need to show a patient if they have a ULI or OOP or HON but only once?
mlmcc
ASKER
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.
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
field5
else if field6="OOPI" then
field6
else if field7="Hon" then
field7
and then you select expect and link the formula field to your main table which sorts patient info ( like
Myformula=patient_info.Id
Rajendra Dewani
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
field5
else if field6="OOPI" then
field6
else if field7="Hon" then
field7
and then you select expect and link the formula field to your main table which sorts patient info ( like
Myformula=patient_info.Id
Rajendra Dewani
Try something like this Oracle pseudo-code.
SELECT Patient_Demo, NVL(Field1, NVL(Field2, NVL(Field3, NVL(Field4, Field5)))) Ext_Pat_IDS
...
SELECT Patient_Demo, NVL(Field1, NVL(Field2, NVL(Field3, NVL(Field4, Field5)))) Ext_Pat_IDS
...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
mlmcc
mlmcc
ASKER
Sorry for the delay, I noticed that there WAS a problem in the linking, it wasn't clear until now.
You'll have to explain this in more detail. It sounds like a real problem on the face of it.