Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    34

    Unanswered: Extracting Duplicates using SQL

    Does any one know the SQL to search through a file and extract the records that are duplicates based on 2 of the 3 fields in the file?

    Example:
    File Layout
    Account Num
    Advisor Num
    Center Num

    File Example
    AccNum AdvNum CenterNum
    123 4567 89999
    123 4567 90000
    222 7869 12345

    So I would want to extract the two records that have 123 for the account num and 4567 for the advisor num.

    It seems that I would have to create a key with AccNum and AdvNum and then search? Not sure how to do any of this?

    Thanks!
    Kelly

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Extracting Duplicates using SQL

    Here is the query that will return your desired results:

    SELECT COUNT(*), AccNum, AdvNum, CenterNum
    FROM Table
    GROUP BY AccNum, AdvNum, CenterNum
    HAVING COUNT(*)>1
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Extracting Duplicates using SQL

    Since you only want two fields make the query like this instead:

    SELECT COUNT(*), AccNum, AdvNum
    FROM Table
    GROUP BY AccNum, AdvNum
    HAVING COUNT(*)>1
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  4. #4
    Join Date
    Nov 2003
    Posts
    34
    Thanks!! This is what I came up with:

    DoCmd.RunSQL ("SELECT Table1.Field1, Table1.Field2, Table1.Field3 INTO NewTable FROM Table1 WHERE (((Table1.Field1) In (SELECT [Field1] FROM [Table1] As Tmp GROUP BY [Field1],[Field2] HAVING Count(*)>1 And [Field2] = [Table1].[Field2]))) ORDER BY Table1.Field1, Table1.Field2;")

    Hopefully this can help someone else too.

    Thanks Again!
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

Posting Permissions

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