Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2005
    Posts
    15

    Unanswered: Help with Duplicate and Delete query

    All,

    I'm fairly new to SQL, but I have a need to perform a couple of tasks in my one table DB.

    Table A consisits of four fields:
    ID - AutoNumber
    Name - Text
    DOB - Text
    SSNbr - Text
    Location - Text Values - Loc1,Loc2,Loc3,Loc4

    I need to perform a query to find duplicate records based on the SSNbr field. This is easy enough, except in the SSNbr field I have some records with Null values in the SSNbr field. This is a problem as some of the records with Null values, are a duplicate record. Is there a way for this query to not ignore rows with a Null?

    Second, I need to create a delete query based on the duplicate query. I want to delete all duplicates on the following criteria. I don't want the duplicate from Loc1 to be deleted. In other words, if I two or more records that were duplicate of each other and one of the locations was Loc1, I want to delete the duplicates from the non-Loc1 location preserving the record from Loc1. If I have duplicates that doesn't reside in Loc1, then it doesn't matter which records get deleted as long as I still have one record for the SSNbr.

    Any help is appreciated.

    TWG

  2. #2
    Join Date
    Dec 2005
    Posts
    15
    Ooops forgot to note this is a table in an Access DB.

  3. #3
    Join Date
    Dec 2005
    Posts
    15
    Following is the SQL statement I'm using in the Duplicate 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 )))
    ORDER BY RoaneCombined.SSNbr;

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Just a quick off the wall poke....

    Back up your table!!!

    DELETE FROM RoaneCombined
    WHERE (SELECT *
    FROM RoaneCombined
    WHERE (((Nz(SSNbr, ""~DUPE~"")) In (SELECT SSNbr FROM RoaneCombined As Tmp GROUP BY Nz(SSNbr, ""~DUPE~"") HAVING Count(*)>1 AND Location <> 'Loc1')))
    ORDER BY SSNbr);

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by CyberLynx
    Self Taught In ALL Environments.....And It Shows!
    It certainly does show. I'm not sure about what you posted above - is it an SQL query?

    Thankfully this is only an MS Access forum.
    Last edited by r123456; 05-24-06 at 09:22.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Dec 2005
    Posts
    15
    Cyberlynx,

    The only thing I see as a possible issue with the delete query is.. I sometimes have a duplicate records that doesn't have Loc1 as the record(s) location. Let's say I have two duplicate records like the following:

    0001 Jones, John 09/29/81 123-45-6789 Loc2
    1002 Jones, John 09/29/81 123-45-6789 Loc4

    Wouldn't the query delete both of these records since neither have Loc1 as it's location? If I have a set of duplicate records and one of those records is Loc1, I want to preserve the Loc1 record, however, if I have duplicate matches that doesn't have Loc1 as a location, then I just need to preserve one of the records and it doesn't matter which of the duplicates gets deleted.

    Thanks,

    TWG

  7. #7
    Join Date
    Dec 2005
    Posts
    15
    Robert,

    Yes it is a delete query, I posted this in the MS Access forum because I am working in a MS Access DB. However, I need to do a little more complex delete query than what MS Access offers through the wizard. I'm new to SQL, so I was hoping to get some assistance on creating a delet query for the scenario mentioned above.

    TWG

  8. #8
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Indeed and I understand your requirement. Cyberlinx has posted a blatantly incorrect query which is totally unacceptable as it can cause more hassle than good to people who are not experts in the SQL language.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  9. #9
    Join Date
    Dec 2005
    Posts
    15
    Robert,

    I understand and thanks, as I don't have enough experience, I wouldn't know it was an incorrect query. Although, as I questioned, I felt like the query would remove some records that shouldn't be removed if we just use the <> Loc1 as a criteria.

    Thanks,

    TWG

  10. #10
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    You could try this method. Make a copy of the structure of the table from which you want to remove the duplicate records.
    Select the table in the Database window, click Copy on the Edit menu, and then click Paste on the Edit menu. In the Paste Table As dialog box, type the name of the table previously copied from the Table Name box, and select the Append Data To Existing Table button. When you click OK, the records will be appended from the original table, without the duplicate records. Don't forget to backup your table first!

Posting Permissions

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