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.

Edited by gloworm - 10 Apr 2012 at 10:08am