Merging Databases

[anduril12]

I have two tables, "Table1" and "Table2" and I would like to merge them, unfortunately I can't create a primary key for Table2.

Now Table1 contains various sections of data "First Name", "Last Name","Addr" and "Phone", Table2 has the exact same columns as well.

What I would like to do is if a record on Table1 and and one on Table2 have the same "First Name" and "Last Name" to copy the "Addr" and "Phone" into table1. Is there anyway to do this?

Table1 has many more records then Table2, but it is older and has more mistakes regarding the phone numbers and addresses, I guess I'm looking for a way to incorporate this new data into the old data.

[Jim Doherty]

If your data is clean and an update can properly be performed and is as easy as just a comparison between one firstname and surname in table1 against a firstname and surname in table2 and you are sure of no duplicity in the table2 then take a look at the following SQL in the SQL window of your query and then switch and look at in in design to get the idea.

UPDATE Table1 INNER JOIN Table2 ON (Table1.LastName = Table2.LastName) AND (Table1.FirstName = Table2.FirstName)
SET Table1.Addr = [Table2].[Addr], Table1.Phone = [Table2].[Phone];

Obviously test it out on some different tables first to ensure no spurious results. This is an SQL method to do the update. But be wary because any duplications will cause erroneous results given firstnames and surname are not what we would call foolproof JOIN entities. I won't go into that too much at this point just now, you will see for yourself when you test it against your data

The other course of action would be to open two recordsets in VBA code and compare the values against each other reporting back to any routine any glaring differences (either by a debug.print process or whatever other method takes your fancy)

Regards

[NeoPa]

I would say the supplied SQL is a perfect answer for all cases. If there are duplicates then the latest one processed will overwrite any earlier ones and you have no good way of selecting which is the most appropriate, but that's the GIGO law for you. The design is not responsible for the data (particularly where it is implemented after the data is entered).

UPDATE Table1 INNER JOIN Table2
    ON (Table1.LastName=Table2.LastName)
   AND (Table1.FirstName=Table2.FirstName)
SET    Table1.Addr=[Table2].[Addr],
       Table1.Phone=[Table2].[Phone]
WHERE  [Table2].[Addr]>''
   OR  [Table2].[Phone]>''

The only material change here being the extra WHERE clause at the end to ensure existing data is not replaced by Nulls in both fields.

[DataAnalyzer]

This falls along the line of address merge/purge products. Does anyone know if such a solution exists for Access?

I've seen this years ago where mail houses would have ways to show duplicate or "near" duplicate records, and you could view and choose which one to keep. Or it would use a priority assignment to specify which list contains the better one and automatically purge the records in the older/lower lists.

[ADezii]

Personally, and as indicated to by Jim Doherty, I like to keep an exact accounting as to which Records were Updated by using 2 Recordsets. The code below will do just that. In reality I would write to a Log Table indicating which Records were Updated (Date/Time) along with the Older and Newer Values for Address and Phone. In this manner, the Update(s) can be reversed if deemed necessary. For the sake of brevity I did not attempt that here. You can also do a Like comparison on the Names and leave it up to the User as to which one will Update the Values in Table1, but this would be more complex.

Dim MyDB As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset

Set MyDB = CurrentDb
Set rst1 = MyDB.OpenRecordset("Table1", dbOpenDynaset)
Set rst2 = MyDB.OpenRecordset("Table2", dbOpenDynaset)

With rst1
  Do While Not rst1.EOF
    Do While Not rst2.EOF
      If (![LastName] = rst2![LastName]) And (![FirstName] = rst2![FirstName]) Then
        'Are the Address and/or Phone different?
        If (![Addr] <> rst2![Addr]) Or (![Phone] <> rst2![Phone]) Then
          .Edit
            ![Addr] = rst2![Addr]
            ![Phone] = rst2![Phone]
              Debug.Print "Phone and Address Information for [" & ![LastName] & ", " & _
                           ![FirstName] & "] Updated on " & Now()
          .Update
            Exit Do
        End If
      End If
        rst2.MoveNext
    Loop
      rst2.MoveFirst
    .MoveNext
  Loop
End With

rst2.Close
rst1.Close
Set rst2 = Nothing
Set rst1 = Nothing

Test Results:

Phone and Address Information for [Fuller, Andrew] Updated on 5/9/2010 12:30:39 PM
Phone and Address Information for [Suyama, Michael] Updated on 5/9/2010 12:37:19 PM
Phone and Address Information for [King, Robert] Updated on 5/9/2010 12:38:52 PM
Phone and Address Information for [Callahan, Laura] Updated on 5/9/2010 1:01:02 PM

P.S. - Besides checking for an exact match on First and Last Name, it also checks to see if there are any differences in either the Phone Number or Address Fields given this matching, and only Updates accordingly.

[NeoPa]

P.S. - Besides checking for an exact match on First and Last Name, it also checks to see if there are any differences in either the Phone Number or Address Fields given this matching, and only Updates accordingly.

I deliberately did NOT go with the either/or option, as it doesn't reflect the requirement. It may turn out to be what is ultimately required, but the question specifically requests new record data replace the old.

[ADezii]

I deliberately did NOT go with the either/or option, as it doesn't reflect the requirement. It may turn out to be what is ultimately required, but the question specifically requests new record data replace the old.

My logic is why Update potentially 90+ Percent of Records when it is not warranted. You do not think that this is relevant or related to the Main Issue? Maybe it isn't, but I felt that it was definitely noteworthy.

[NeoPa]

My bad. I misread (or misunderstood) your code.

Incidentally, when I said that, I was not referring to the idea of skipping records that already matched, but only how you were doing the comparison. In the event, it turned out that was perfectly correct anyway. More than that, it was essentially the same as I'd already suggested in my SQL.

I must have been having a senior moment :(

[ADezii]

My bad. I misread (or misunderstood) your code.

Incidentally, when I said that, I was not referring to the idea of skipping records that already matched, but only how you were doing the comparison. In the event, it turned out that was perfectly correct anyway. More than that, it was essentially the same as I'd already suggested in my SQL.

I must have been having a senior moment :(

Don't feel bad, my Senior Moments are the 'Norm' rather that the Exception! (LOL)