Getting multiple results and cannot figure it out

[thegloworm]

I am using Crystal Reports XI and am having some issues getting correct data. I think the tables are not joined correctly, but I do not have a resource to tap for help here.

I have asked if there is someone here that is more familiar with the tables and how they need to be joined and the answer was NO.

I created a Command table using this query:

SELECT DISTINCT pm.provider_id,
pm.description,
ppm.practice_id,
p.practice_name as practice_name
FROM provider_practice_mstr ppm INNER JOIN practice p on ppm.practice_id = p.practice_id INNER JOIN provider_mstr pm ON ppm.provider_id = pm.provider_id
WHERE ppm.attending_ind = 'Y'
and ppm.delete_ind = 'N'
AND pm.delete_ind = 'N'


I then added it to this report, using the same links as another report that happens to run fine.


However, when I run it, I get the same exact results for every Physician in the report. For example, if John Doe had had heart surgery, he would be under every doctor on the report, whether the doctor saw him or not.


I have double checked the links and they are identical to the report that runs fine.

I have grouped on the doctors name, but cannot group on patient information. Reason being that a patient could have more than one contact with a particular doctor. if I group on patient data, I will lose a lot of the data.

The same thing applies to using the diagnosis for grouping, can't use it because it may exclude data.

I added the following tables to the report:

Command
Patient_Encounter
Provider_Mstr



You can see them in the picture below. The report that runs fine is linked exactly as this one, from the Person table on.

 

[Madawc]

Why not try beginning again, giving Crystal the table links and record selections you want? It is effcient and reliable for simple links.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Tatertot45]

I would look at the linking again and check why you have Description linked together on the right. If those are free text fields, you could get unexpected results with many-to-many relationships being created.

That is just what I see.

Tatertot45
CRCP
A+, Network+
CCNA
MCP