removing duplicate records based on duplicate address

[johnmerlino]

EDITED removing duplicate records even when zip codes aren't really duplicates

I am editing the question. It didn't make sense what I wrote.

I have this query:

SELECT * INTO KeepThese_SummaryJudgment
FROM final_output_summary_judgment AS f LEFT JOIN (SELECT t.fullName FROM final_output_summary_judgment AS t GROUP BY t.fullName HAVING Count(t.fullname)>=6)  AS Ex ON f.fullName=Ex.fullName
WHERE Ex.fullname Is Null;

Although it is supposed to remove any duplicates, it doesn't realize that where the 5 digit zip codes are the same that these should count as duplicates, but it doesn't count them as duplicates because they are not the same:

Schmidt,James    6800 NW 24 TER    FORT LAUDERDALE    FL    33309    
Schmidt,James    6800 NW 24 TER    FORT LAUDERDALE    FL    33309-1412

Is there a way to get it so that it keeps only one of these records only because everything matches, except this part "-1412" included in one of the zip codes. I would only want to preserve the record with the longer zip code in these types of situations.

Thanks for response.

 

[HiTechCoach]

Try using just the Left() 5 digits of the ZIP code for the comparison.