Results 1 to 8 of 8
  1. #1
    madamson86 is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2011
    Posts
    6

    SQL code to remove duplicates from table then import into a existing table

    Hello



    I am hoping someone is able to help me, I have setup a new database but need some assistance in setting up some SQL code which I havent done before.

    I have two tables, one is linked to Sharepoint (Completions) and one is linked to an excel file (Import). I need to run a query that goes through the Import table and removes any duplicate lines where the [Matter Number] and [Completion Date] are the same so that only one line is remaining. Once this has been done I need to compare the import table against the Completions table and again remove any entries from the import file where the [Matter Number] and [Completion Date] already appear in the Completions table.

    Then anything that is left in the Import table needs to be moved across to the Completions table.

    Thanks in advance for your assistance.

    Cheers
    Mike

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    what have you tried, Mike?

    Surely any setup like this is part of a business that's run by intelligent people, no?

  3. #3
    madamson86 is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2011
    Posts
    6
    Hi

    What it is we use to use a Shared excel document for this which I did all the coding in VBA without any issues. But we have had to move it into access as it crashes due to the number of users.

    To get anything changed at this places takes an eternity so try and do what I can myself instead of getting IT involved.

    I managed to do the below coding but this just moves everything so need to work out how to remove the duplicate entrys first. I have never done SQL so not sure where to start.

    Code:
    INSERT INTO Completions
    SELECT DISTINCTROW Import.*
    FROM Import;
    Regards
    Mike

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    would you like an example of how to remove duplicate entries from an access table? I have an ms access example function for this. do you want it?

  5. #5
    madamson86 is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2011
    Posts
    6
    If you could send that across that would be great, gives me a starting point.

    Cheers
    Mike

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Code:
    Function DeleteDups(tblName As String)
    
    On Error Resume Next
    
    '******************************************************************************
    '                                                                             *
    'Author: Adam Evanovich                                                       *
    'Date: 11/11/2010                                                             *
    'Purpose: Deletes duplicate records in a single table.                        *
    '                                                                             *
    'Arguments:                                                                   *
    'tblName > Your table.                                                        *
    '                                                                             *
    '******************************************************************************
    
    Dim tempTbl As String
    Dim tempSql As String
    Dim db As DAO.Database
    Set db = CurrentDb
    tempTbl = db.TableDefs(tblName).Name
    
       If err.Number = 3265 Then
          MsgBox "Table does not exist!"
             GoTo Exit_Handle
       Else
          tempTbl = tblName & "2"
       End If
    
    On Error GoTo Err_Handle
    
       tempSql = "SELECT DISTINCT *" & _
                " INTO " & tempTbl & _
                " FROM " & tblName
    
          db.Execute tempSql, dbFailOnError
          db.TableDefs.Delete (tblName)
          DoCmd.Rename tblName, acTable, tempTbl
          db.TableDefs.Refresh
    
    Exit_Handle:
       db.Close
          Set db = Nothing
             Exit Function
    
    Err_Handle:
       MsgBox err.Description
          Resume Exit_Handle
    
    End Function '//LL

  7. #7
    madamson86 is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2011
    Posts
    6
    Hi

    Which columns does this look at when deleting duplicate rows? Does is use two criteria when deleting?

    Sorry for all the questions.

    Mike

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    do you know vba at all?

    it looks at all columns, sir. that's what the ''*'' symbol does in the ''tempSQL'' variable. modify the sql variable called ''tempSQL'' to get this to happen. can you do that?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 07-24-2012, 07:50 PM
  2. Remove Duplicates Based on Criteria
    By suryaprasad in forum Access
    Replies: 0
    Last Post: 04-07-2011, 10:50 PM
  3. How to remove duplicates
    By TonyBender in forum Access
    Replies: 0
    Last Post: 10-21-2009, 10:27 PM
  4. Using VBA create a new table from an existing table
    By KramerJ in forum Programming
    Replies: 0
    Last Post: 03-25-2009, 04:07 PM
  5. Import multiple dbf files into existing table?
    By sbg2 in forum Import/Export Data
    Replies: 0
    Last Post: 06-12-2006, 02:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums