Report Groupings - Hiding duplicates value in different Group lev

[ecwhite]

Hello Please help,

I have a report with three group headers that I created. One patient can
have many procedures and under the same procedure, multiple diagnoses code.
I created group header on patientid, procedure code, and on diagnoses code.

What i want is say for patient123 as shown below, if he has diagnoses 234
showing under procedure1, even if there is diagnoses 234 again for
procedure2, i don't want to show it again on the report. I have tried hiding
and other things but i can't seem to get it to work please see example below.

patient123
procedure1
Diagnoses 234
123
134
procedure2
Diagnoses 234
923
488
123
patient124 etc

What I want to achieve will look like below

patient123
procedure1
Diagnoses 234
123
134
procedure2
Diagnoses 234 will not show up again for
this patient
923 will be on report
488 will be on report
123 will not show up again
for this patient
patient124 etc

Thanks ecwhite.

 

[KARL DEWEY]

These three queries seems to work ----
ECWhite_Diagnoses --
SELECT Q.Patient, Q.Procedure, Q.Diagnoses, (SELECT COUNT(*) FROM ECWhite Q1
WHERE Q1.[Patient] = Q.[Patient]
AND Q1.[Diagnoses] < Q.[Diagnoses])+1 AS Rank
FROM ECWhite AS Q
ORDER BY Q.Patient, Q.Procedure, Q.Diagnoses DESC;

ECWhite_RPT_1 ---
SELECT ECWhite_Diagnoses.Patient, ECWhite_Diagnoses.Diagnoses,
ECWhite_Diagnoses.Rank
FROM ECWhite_Diagnoses
GROUP BY ECWhite_Diagnoses.Patient, ECWhite_Diagnoses.Diagnoses,
ECWhite_Diagnoses.Rank;

SELECT ECWhite_Diagnoses.Patient, First(ECWhite_Diagnoses.Procedure) AS
FirstOfProcedure, ECWhite_Diagnoses.Diagnoses, ECWhite_Diagnoses.Rank
FROM ECWhite_Diagnoses INNER JOIN ECWhite_RPT_1 ON (ECWhite_Diagnoses.Rank =
ECWhite_RPT_1.Rank) AND (ECWhite_Diagnoses.Diagnoses =
ECWhite_RPT_1.Diagnoses) AND (ECWhite_Diagnoses.Patient =
ECWhite_RPT_1.Patient)
GROUP BY ECWhite_Diagnoses.Patient, ECWhite_Diagnoses.Diagnoses,
ECWhite_Diagnoses.Rank;