My database looks something like this
Table 1 Table 2
Case Physician Code -------> Physician Code Name
123 1 1 Dr Smith
123 2 2 Dr Roberts
124 1 3 Dr Jones
125 1
and so on ....
I would like this to show up as
Case Physician1 Physician 2
123 Dr Smith Dr Roberts
124 Dr Smith
125 Dr Smith
I brought in table 2 twice and aliased it as Table2_1.
@Physician 1 = if {Table1.Physician_Code} = 1 THEN {Table2.NAME}
@Physician 2 = if {Table2_1.Physician_Code} = 2 and {Table2.Physician_Code} = 1
THEN {Table.NAME}
What I get is
123 Dr Smith
123 Dr Smith Dr Robert
124 Dr Smith
125 Dr Roberts
How can I eliminate the "duplicate" lines where the data fits the condition of having a value for Physician 1. Does it have anything to do with a whileprintingrecords formula?
Thanks for the help!