Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Deleting duplicate records

    I have a simple table with 3 fields, ID, Description, and RefNumber:

    Note that ID is not unique.

    ID Description RefNumber

    1 Sector1 3373
    2 Sector1 233
    3 Sector1 9011
    4 Sector1 877
    1 Sector2 123
    2 Sector2 3373
    1 Sector3 139
    2 Sector3 1001
    3 Sector3 3373

    I need a way of removing all records where the RefNumber field has duplicates, except the first instance. So in the above example, ID 1 with Sector1 with RefNumber 3373 would remain, but all other records with RefNumber of 3373 would be removed. Any simple solutions. I can think of a few complex solutions, but I'd like to see a simple one!

  2. #2
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Deleting duplicate records

    Originally posted by bcass
    I have a simple table with 3 fields, ID, Description, and RefNumber:

    Note that ID is not unique.

    ID Description RefNumber

    1 Sector1 3373
    2 Sector1 233
    3 Sector1 9011
    4 Sector1 877
    1 Sector2 123
    2 Sector2 3373
    1 Sector3 139
    2 Sector3 1001
    3 Sector3 3373

    I need a way of removing all records where the RefNumber field has duplicates, except the first instance. So in the above example, ID 1 with Sector1 with RefNumber 3373 would remain, but all other records with RefNumber of 3373 would be removed. Any simple solutions. I can think of a few complex solutions, but I'd like to see a simple one!
    Try this:
    DELETE <TblName>.ID, <TblName>.Description, <TblName>.RefNumber
    FROM <TblName>
    WHERE (((<TblName>.Description) Not In (SELECT Min(<TblName>.Description) AS MinOfDescription
    FROM <TblName>)) AND ((<TblName>.RefNumber) In (SELECT <TblName>.RefNumber
    FROM <TblName>
    GROUP BY <TblName>.RefNumber)));
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    That doesn't seem to quite work. Heres what I start with:

    ID Description RefNumber

    1 PF1 3373
    2 PF1 257
    3 PF1 2002
    1 RN15 3373
    2 RN15 257
    3 RN15 124
    4 RN15 N/A
    5 RN15 5501
    6 RN15 339

    And this is the result your code gives:

    ID Description RefNumber

    1 PF1 3373
    2 PF1 257
    3 PF1 2002

    This is what I want it to give:

    ID Description RefNumber

    1 PF1 3373
    2 PF1 257
    3 PF1 2002
    3 RN15 124
    4 RN15 N/A
    5 RN15 5501
    6 RN15 339

  4. #4
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Heres how I expressed your code:

    Code:
    DoCmd.RunSQL _
        "DELETE * FROM tblConvertedPacks " & _
        "WHERE (((SEPackNumber) " & _
        "NOT IN (SELECT Min(SEPackNumber) AS MinOfSEPackNumber " & _
        "FROM tblConvertedPacks)) " & _
        "AND ((PremierPackNo) " & _
        "IN (SELECT PremierPackNo " & _
        "FROM tblConvertedPacks " & _
        "GROUP BY PremierPackNo)));"
    And I've used the real field names in my code. From my example, the actual fields are:

    ID = ID
    Description = SEPackNumber
    RefNumber = PremierPackNo

  5. #5
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by bcass
    Heres how I expressed your code:

    Code:
    DoCmd.RunSQL _
        "DELETE * FROM tblConvertedPacks " & _
        "WHERE (((SEPackNumber) " & _
        "NOT IN (SELECT Min(SEPackNumber) AS MinOfSEPackNumber " & _
        "FROM tblConvertedPacks)) " & _
        "AND ((PremierPackNo) " & _
        "IN (SELECT PremierPackNo " & _
        "FROM tblConvertedPacks " & _
        "GROUP BY PremierPackNo)));"
    And I've used the real field names in my code. From my example, the actual fields are:

    ID = ID
    Description = SEPackNumber
    RefNumber = PremierPackNo

    My question is where is SEPackNumber and PremierPackNo coming from? The table that you are giving to me has only "ID, Description, RefNumber". This is supposed to be a recursive query so that it is finding the lowest Description and not deleting that one while deleting all the rest that have multiple RefNumbers. Is your underlying table structure different than you column titles?


    DELETE * FROM tblConvertedPacks
    WHERE (((Description)
    NOT IN (SELECT Min(Description) AS MinOfSEPackNumber
    FROM tblConvertedPacks))
    AND ((RefNumber)
    IN (SELECT RefNumber
    FROM tblConvertedPacks
    GROUP BY RefNumber)));
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  6. #6
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    [SIZE=1]Originally posted by jimpen
    My question is where is SEPackNumber and PremierPackNo coming from? The table that you are giving to me has only "ID, Description, RefNumber". This is supposed to be a recursive query so that it is finding the lowest Description and not deleting that one while deleting all the rest that have multiple RefNumbers. Is your underlying table structure different than you column titles?
    Sorry, I have confused things a bit. In my first post, the field names were just examples. The real field names are as follows:

    ID SEPackNumber PremierPackNo

    1 Sector1 3373 *
    2 Sector1 233
    3 Sector1 9011
    4 Sector1 877
    1 Sector2 123
    2 Sector2 3373 #
    1 Sector3 139
    2 Sector3 1001
    3 Sector3 3373 #

    This table is a temp table, which stores the results of a query. I need to remove the duplicates (in the fashion previously described) from this temp table.

    * = keep this record, since it is the first instance of a duplicate;
    # = delete these, since they are multiple instances of a duplicate;

    In addition (and I've only just remembered about this) - I need to be able to specify what PremierPackNo's qualify as criteria for deletion (stored in another table - tblExceptions - just 1 field - PremierPackNo). Any PremierPackNo's not in tblExceptions will not be processed by the/your remove duplicates query.

    In the interim, I have actually solved this problem, but it is far from elegant - involving 2 append queries and 2 delete queries. If you can come up with something better I'd love to see it!

  7. #7
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    This should bring you down to two queries. The delete and then the append to the live table. Just very recursive and referential. I tried it on a test table and it seems to work.

    Just a side note, please use the real col names or be prepared to do a search and replace on what we send back to you. That's what caused some of the confusion on my queries.

    Try this:
    Dim SQL as string

    SQL="DELETE tblConvertedPacks.SEPackNumber, tblConvertedPacks.PremierPackNo, * " & _
    "FROM tblConvertedPacks " & _
    "WHERE (((tblConvertedPacks.SEPackNumber) Not In (SELECT Min(SEPackNumber) AS MinOfSEPackNumber " & _
    "FROM tblConvertedPacks)) AND ((tblConvertedPacks.PremierPackNo) In (SELECT PremierPackNo " & _
    "FROM tblConvertedPacks WHeRE PremierPackNo IN (SELECT PremierPackNo " & _
    " FROM tblExceptions " & _
    " GROUP BY PremierPackNo ) " & _
    "GROUP BY PremierPackNo))); "

    docmd.runsql sql, true


    Have a good day.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  8. #8
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Thanks for your help. I'll give it a whirl now.

Posting Permissions

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