Delete Duplicate Records fro two fields

[vangundy]

How can I delete records where the FName and LName are duplicate?

Ex:

LName FName

Smith Joe
Smith John
Smith Joe
Brown Dennis


As you can see there is a duplicate for LName and FName. The final Outcome shoudl be:

LName FName

Smith Joe
Smith John
Brown Dennis

 

[Remou]

Hi
This may help:
Delete Duplicates (Save One From Each Group)
faq701-5721

 

[PHV]

Hopefully your table contains a PrimaryKey:
DELETE FROM yourTable
WHERE [PK field] Not In (SELECT Max([PK field]) As x
FROM yourTable GROUP BY LName,FName);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vangundy]

Actually I do not have a unique... I am taking an extract from another system and the way there backend is created there is no unique key..... (!!!)

 

[PHV]

I am taking an extract from another system
How are you doing that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vangundy]

I am using a macro to import the csv file into my table... I do not have aunique key... when I try to move the data usinf distinct and into a new table I get a msg that this field is to small..... when using memo fields