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.

[attachment=52298:Crystal.jpg]

Just some general suggestions

The behavior you describe (right side records repeated for every record on the left side) usually occurs because the two tables aren't fully jointed. The join is what controls (prevents) the repetition. I see you checked your links, but re-check, because usually this is why.

If I read your post right, you have three tables joined. You might try just joing the two, make sure that works as expected, then add on the third.

Good luck with your project.