Deleting duplicate records

Topic

New Reply

Hi not a straightforward de-dup (for me anyway). I have a file which I am matching across many fields. There can be no primary key on this table, but the core Id is PersonId. Each person Id can have many records. I also need to match two fields, CustomerId and TempId, to create a “Yes” in the Match Field.

Therefore I am left with something like this:

PersonId CustomerId TempId Match
12 23 23 Y
12 23 27 N
12 22 24 N

What I want to do is a bit complicated. Where there is ore than one instance of person Id, I only want to keep the record which has Match ‘y’, but where there is no Match= ‘Y’, I want to leave the No’s. Can anyone explain to me how I do this in Access 2003? I can follow simple code only.

Much obliged for your help.

Blitz

Reply | Quote

Replies

Should it be presumed that all the records have the Match field filled in or do you need code to set the Match field value, too?

Reply | Quote

I suppose this SQL statement should do it, admiting the match value is already filled in:

Code:

DELETE * FROM T2 WHERE PersonID IN (Select PersonID From T2 Group By PersonID Having Count(PersonID) >1) AND  EXISTS (SELECT * FROM T2 T WHERE T.PersonID=T2.PersonID and Match=”Yes”) AND T2.Match=”No”

This will delete records where you have more than one instance with the same personID, for that personID there is at least a record with a Match value of Yes a[/B]nd the record to delete will have a Match value of No.

I named the table T2 (I already had a T1 in my test database :)), so just change the name for your actual table name. To use this just create a new query, don’t add any tables, choose SQL View and paste the code there.

Once you get the code there, you can choose datasheet view to see the records to be deleted. I would also recommend creating a table copy, just in case I missed something ;). I always feel it is risky to offer a delete query to work over someone else’s data.

Reply | Quote

Hi Ruiri – yes I’ve got the Match y/n filled. Thanks kindly for the help – will give it a try with a backuptable in place.

Blitzy

Reply | Quote