Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting duplicate records

    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

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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?
    Last edited by ruirib; 2011-07-06 at 11:51.

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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 and 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.
    Last edited by ruirib; 2011-07-07 at 06:36. Reason: typo

  4. #4
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •