deleting Duplicates

[wdbouk]

Hi ,

I know that this question could be a basic one. But how can i delete the duplicates from my data. For example
if I have 5 duplicate rows how can I tell access to eliminate 4 of them and keep the 5th.
Thx

 

[PHV]

Hopefully you have a field that uniquely identify a row, say myID:
DELETE FROM yourTable
WHERE myID Not In (SELECT Max(myID) FROM yourTable GROUP BY dupField1, dupField2, ..., dupFieldn)


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

 

[wdbouk]

Actually two fields identify a unique row, myID and Date. How could the command be modified?

 

[PHV]

DELETE FROM yourTable
WHERE Format([Date],'yyyymmdd') & myID Not In (SELECT Max(Format([Date],'yyyymmdd') & myID) FROM yourTable GROUP BY dupField1, dupField2, ..., dupFieldn)

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

 

[wdbouk]

Hello again,

the query now is
DELETE *
FROM Dup
WHERE Format([FromDate],'mmddyyyy') & IssrNo Not In (SELECT Max(Format([FromDate],'mmddyyyy') & IssrNo ) FROM Dup GROUP BY IssrNo,FromDate,Rtg,ToDate,DefDate );

I have around 58,000 rows and 5 columns in the Dup table. Th query is taking forever to run. Does it make sense? I have a new laptop with 1 geg memory.I am wondering if there is something that could be done regarding the time. After half an hour the run query was still at the begining.
thx

 

[PHV]

And it will delete nothing as you group by IssrNo,FromDate that are known to uniquely identify a row ...

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