Comparing two tables

[crowegreg]

I've written this code to compare two tables, which are identical in design, to compare each field's data, and if their is a difference, to write this record to another table. Here's the code:

        Set dbase = CurrentDb
        
        Set rs = New ADODB.Recordset
        strSQL = "SELECT Hyp_Joe.Field1, Hyp_Joe.Field2, Hyp_Joe.Field3, Hyp_Joe.Field4, Hyp_Joe.PID"
        strSQL = strSQL & " FROM Hyp_Joe INNER JOIN Hyp_022014 ON Hyp_Joe.PID = Hyp_022014.PID"
        strSQL = strSQL & " ORDER BY Hyp_Joe.PID"
        rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
            Do Until rs.EOF
                'Assign variable to first Position ID within recordset
                strPID = rs.Fields(4)
        
            'Create recordset of records within PM_Archive using dteOldThurDate and Position ID from above recordset
            Set rs2 = New ADODB.Recordset
            strSQL = "SELECT Hyp_022014.ColA, Hyp_022014.ColB, Hyp_022014.ColC, Hyp_022014.ColD, Hyp_022014.PID"
            strSQL = strSQL & " FROM Hyp_022014 INNER JOIN Hyp_Joe ON Hyp_022014.PID = Hyp_Joe.PID"
            strSQL = strSQL & " WHERE (((Hyp_022014.PID) = "
            strSQL = strSQL & Chr(34) & strPID & Chr(34) & "))"
            strSQL = strSQL & " ORDER BY Hyp_022014.PID"
            
            'Open recordset
                rs2.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
                
                        strSQL = "SELECT Hyp_Audit.Position_ID, Hyp_Audit.Data_Field, Hyp_Audit.SS_Data, Hyp_Audit.Hyp_Data FROM Hyp_Audit"
                        
                        For i = 0 To 3
                            If rs.Fields(i) <> rs2.Fields(i) Then
                                Set rs3 = New ADODB.Recordset
                                'Create recordset to add new records to PM_Audit
                                rs3.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
                                    'Add new record
                                    rs3.AddNew
                                        'Assign variables to recordset fields
                                        rs3![Position_ID] = strPID
                                        rs3![Data_Field] = dbase.TableDefs("Hyp_Joe").Fields(i).Name
                                        rs3![SS_Data] = rs.Fields(i)
                                        rs3![Hyp_Data] = rs2.Fields(i)
                                    rs3.Update
                                rs3.Close
                                Set rs3 = Nothing
                            End If
                         Next
                rs2.Close
                Set rs2 = Nothing
                
            rs.MoveNext
            Loop
        rs.Close
        Set rs = Nothing
        
        Set dbase = Nothing

This all works correctly. My problem is with the data. An example is if the data within 1 field in 1 table is "Application" and within the other table it is "Applications" it writes a record to the third table. Are my only ways to prevent this record from being written to the third table is to use LIKE statements? Another example is the word "Manager" and various forms of "Man."


Thanks in advance!!

[June7]

Yep. Data inconsistencies do complicate. Pattern matching will never catch every case. Certainly can't take into account misspelling. What if one of the p's is missing? Would you want that record to copy?

[orange]

A concept that you might consider depending on volumes is to have a tblSimilarButNotOK
tblSimilarButNotOK
RecId autonumber PK
AuthorizedWord Text ( you most accepted term)
OtherTerm Text

You could populate this with some trial and error and testing

1,Manager, Man.
2,Manager, Mgr
3 Manager, Maanger
4,Application, Applications
5,Application, Appln
...

You add the OtherTerms with the AuthorizedWord and make a new record.

Once you have identified the OtherTerms/Misspellings, you can now search your text; look up the OtherTerm/Misspelling and get the AuthorizedWord and replace the "bad term" with the AuthorizedWord.

This sort of thing is often done where there are a number of misspellings/typos.

Just a thought for consideration. Depends on your time, volume of data, and how accurate the "final" has to be.

[crowegreg]

Thanks for replying. Bad data is bad data. I wanted to make sure there wasn't something simple that I was overlooking.

Orange, your suggestion is great, unfortunately it won't work for me at this time.