Results 1 to 2 of 2
  1. #1
    newbieX is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    111

    Compare two fields from separate tables and message user what unique IDs are duplicate

    I want to check the main table and make sure that duplicate data from the temp table does not exist before importing into the table.

    Main Table

    Code:
    UID            Car     Owner
    123D        Honda    Brown
    125F        Honda    Jones
    457A        Toyota    Smith
    345RT        VW        Jones

    Temp Table Example 1

    Code:
    123D        Honda    Brown
    457A        Toyota    Smith
    999S        Honda    Green
    Temp Table Example 2

    Code:
    999S        Honda    Green
    896A        VW        James
    When button is pushed it compares Main table UID to Temp table UID and displays message (if match is found) informing user that duplicate data exists for that UID and will be deleted from Temp table
    A message appears for each match found. After (or if) messages are viewed then code deletes row(s) where data matches.

    After duplicate data is deleted from Temp table, remaining data gets appended to Main Table (I have working code for this)

    Code I have to delete data is:
    Code:
    DELETE TempTable.*
    FROM TempTable JOIN TempTable ON MainTable.UID = TempTable.UID;
    What I don't know how to do is get separate messages for each match.

    For example:



    For Temp Table Example 1, the user is notified that data for UID 123D and 457A are duplicates and will not be imported into Main table but deleted from temp table.

    For Temp table Example 2, no messages gets displayed and all data from Temp table 2 is imported into the main table.

    Any and all help appreciated.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What I don't know how to do is get separate messages for each match.
    I threw this together. Pretty sure it works. TRY THIS ON A COPY OF YOUR DB!!
    I don't use spaces in object names, so check names in the code!!


    You need to add the code to append remaining records the the main table.
    Code:
    Public Sub CompareTables()
        '
        'this code loops through a recordset and deletes
        'duplicate records from the tempTable
        '
        '
        Dim r As DAO.Recordset
        Dim sSQL As String
        Dim Msg As String, Title As String, DeleteMsg As String
        Dim Style, Response
    
        'message box stuff
        Style = vbYesNo + vbCritical + vbDefaultButton2
        Title = "Duplicate record found"
        Msg = "Are you sure you want to delete this record?"
    
        'duplicates query
        sSQL = "SELECT TempTable.UID, TempTable.Car, TempTable.Owner"
        sSQL = sSQL & " FROM MainTable INNER JOIN TempTable ON MainTable.UID = TempTable.UID;"
        Set r = CurrentDb.OpenRecordset(sSQL)
    
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            '        MsgBox r.RecordCount
            r.MoveFirst
    
            Do While Not r.EOF
                'message for each record
                DeleteMsg = ""
                DeleteMsg = Msg & vbNewLine & vbNewLine & "duplicate data exists for UID = " & r!UID
    
                'ask to delete the record
                Response = MsgBox(DeleteMsg, Style, Title)
                If Response = vbYes Then
                    'delete record
                    CurrentDb.Execute "DELETE FROM TempTable WHERE UID = '" & r!UID & "'", dbFailOnError
                End If
                r.MoveNext
            Loop
        End If
    
        r.Close
        Set r = Nothing
    
    
    
        'code to append remaining records in tempTable to MainTable goes here
    
    
    End Sub
    What do you think?

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

Similar Threads

  1. Replies: 8
    Last Post: 06-05-2015, 02:17 PM
  2. Replies: 10
    Last Post: 11-25-2014, 06:35 PM
  3. Compare if duplicate and display message
    By chotroi in forum Access
    Replies: 7
    Last Post: 03-22-2014, 02:53 PM
  4. New user - separate a table into two tables
    By Henry_Reimer in forum Database Design
    Replies: 19
    Last Post: 10-08-2011, 10:19 PM
  5. Replies: 3
    Last Post: 09-11-2011, 06:38 PM

Tags for this Thread

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