Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005

    Unanswered: Need assistance with Delete query


    I am fairly new to SQL, and am working with a one table DB in MS Access. I need to do a Deletion query based on a Duplicates query I created. Below is the Dulpicate query..

    SELECT [RoaneCombined].[SSNbr], [RoaneCombined].[ID], [RoaneCombined].[Name], [RoaneCombined].[DOB], [RoaneCombined].[Location]
    FROM RoaneCombined
    WHERE (((RoaneCombined.SSNbr) In (SELECT [SSNbr] FROM [RoaneCombined] As Tmp GROUP BY [SSNbr] HAVING Count(*)>1 ))) OR (((RoaneCombined.Name) In (SELECT [Name] FROM [RoaneCombined] As Tmp GROUP BY [Name],[DOB] HAVING Count(*)>1 And [DOB] = [RoaneCombined].[DOB])))
    ORDER BY [RoaneCombined].[Name];

    What I need to do is delete all of the duplicates while preserving one record per SSNbr or Name/DOB match. If any of the matches within the SSNbr or Name/DOB match contains Loc1 as its location, then I want to preserve the Loc1 record and delete all of the other duplicates for that group

    Example 1:
    ID Name DOB SSNbr Location
    0001 Jones, John 09/29/81 123-45-6789 Loc1
    1002 Jones, John 09/29/81 123-45-6789 Loc4
    1987 Jones, John 09/29/81 123-45-6789 Loc3

    Example 2:

    ID Name DOB SSNbr Location
    3325 Dean, Martin 05/29/77 123-34-6789 Loc2
    1467 Dean, Martin 05/29/77 123-34-6789 Loc4
    6745 Dean, Martin 05/29/77 123-34-6789 Loc3

    In example 1, I'd like to keep the first record that has Loc1 as it's location and delete the remaining records for that group. In example 2, I just need to keep one record and delete the other two as the locations for that group doesn't contain any greater importance.

    I hope I've given enough detail without going overboard and thanks in advance for any assistance you can lend.


  2. #2
    Join Date
    Aug 2005
    D/FW, Texas, USA
    This will always save the lowest Location. (So Loc1 will be saved if it's there otherwise which ever one is the lowest .)

    DELETE FROM RoaneCombined
    FROM RoaneCombined a
      JOIN (
        SELECT Name, DOB, SSNbr, Min(Location) as MinLoc
        GROUP BY Name DOB SSNbr
        HAVING Count(*)>1
      ) b
        ON a.Name = b.Name AND a.SSNbr = b.SSNbr AND a.DOB = b.DOB
    WHERE a.Location <> b.MinLoc
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

Posting Permissions

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