urgent: need help on removing semi-duplicate records

[Former Member]

Post Author: mnguyen2032

CA Forum: Crystal Reports

when I join two tables, the resulting dataset is the following:

ID DATE FSD_ID VALUE ACCOUNT_ID PAT_NAME RECORDED_TIME Line7978945 03-Apr-08 108207 Medical 70464509 WILEY,DEJAHN 4/3/2008 3:22:00 AM 217978945 03-Apr-08 108207 Medical;Pediatric 70464509 WILEY,DEJAHN 4/3/2008 3:58:00 AM 17978965 03-Apr-08 108229 Medical 70464517 THORSEN,ZITA M 4/4/2008 3:22:00 AM 117978976 03-Apr-08 108234 Medical 70464519 SNYDER,TORI M 4/5/2008 3:22:00 AM 1

Question: How do I tell Crystal Report to pull only one distinct ID record into my report SINCE you can see that we have 2 rows of ID = 7978945 above?

Thanks so much in advance!!!!!

[Former Member]

Post Author: mnguyen2032

CA Forum: Crystal Reports

Thanks. I did use the inner join......The problem is that both tables have the FSD_ID field. The first table has FSD_ID as primary key and the 2nd table has FSD_ID and LINE as primary key. When I joined table 1 to table 2, I have multiple records since table 2 has multiple rows with the same FSD_ID value but with different LINE values......

I did try to use Maximum () hoping to retrieve the row with the highest number in the LINE column in the "REcord Selection Formula Editor" window, but Crystal Report doesn't like that. It would be nice if there is a way to use LINE or RECORDED_TIME as criteria to pick a single record in case of same multiple FSD_ID records????

[Former Member]

Post Author: AJDuck1

CA Forum: Crystal Reports

change your left outer join ton an inner join in the links window

if you explicitedly want to use DISTINCT keyword consider a view