By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

Merging Databases

P: 6
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.
May 8 '10 #1
Share this Question
Share on Google+
8 Replies


Jim Doherty
Expert 100+
P: 897
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.
Expand|Select|Wrap|Line Numbers
  1. UPDATE Table1 INNER JOIN Table2 ON (Table1.LastName = Table2.LastName) AND (Table1.FirstName = Table2.FirstName)
  2. 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
May 9 '10 #2

NeoPa
Expert Mod 15k+
P: 31,276
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).
Expand|Select|Wrap|Line Numbers
  1. UPDATE Table1 INNER JOIN Table2
  2.     ON (Table1.LastName=Table2.LastName)
  3.    AND (Table1.FirstName=Table2.FirstName)
  4. SET    Table1.Addr=[Table2].[Addr],
  5.        Table1.Phone=[Table2].[Phone]
  6. WHERE  [Table2].[Addr]>''
  7.    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.
May 9 '10 #3

P: 15
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.
May 9 '10 #4

ADezii
Expert 5K+
P: 8,607
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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst1 As DAO.Recordset
  3. Dim rst2 As DAO.Recordset
  4.  
  5. Set MyDB = CurrentDb
  6. Set rst1 = MyDB.OpenRecordset("Table1", dbOpenDynaset)
  7. Set rst2 = MyDB.OpenRecordset("Table2", dbOpenDynaset)
  8.  
  9. With rst1
  10.   Do While Not rst1.EOF
  11.     Do While Not rst2.EOF
  12.       If (![LastName] = rst2![LastName]) And (![FirstName] = rst2![FirstName]) Then
  13.         'Are the Address and/or Phone different?
  14.         If (![Addr] <> rst2![Addr]) Or (![Phone] <> rst2![Phone]) Then
  15.           .Edit
  16.             ![Addr] = rst2![Addr]
  17.             ![Phone] = rst2![Phone]
  18.               Debug.Print "Phone and Address Information for [" & ![LastName] & ", " & _
  19.                            ![FirstName] & "] Updated on " & Now()
  20.           .Update
  21.             Exit Do
  22.         End If
  23.       End If
  24.         rst2.MoveNext
  25.     Loop
  26.       rst2.MoveFirst
  27.     .MoveNext
  28.   Loop
  29. End With
  30.  
  31. rst2.Close
  32. rst1.Close
  33. Set rst2 = Nothing
  34. Set rst1 = Nothing
  35.  
Test Results:
Expand|Select|Wrap|Line Numbers
  1. Phone and Address Information for [Fuller, Andrew] Updated on 5/9/2010 12:30:39 PM
  2. Phone and Address Information for [Suyama, Michael] Updated on 5/9/2010 12:37:19 PM
  3. Phone and Address Information for [King, Robert] Updated on 5/9/2010 12:38:52 PM
  4. 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.
May 9 '10 #5

NeoPa
Expert Mod 15k+
P: 31,276
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.
May 9 '10 #6

ADezii
Expert 5K+
P: 8,607
@NeoPa
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.
May 9 '10 #7

NeoPa
Expert Mod 15k+
P: 31,276
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 :(
May 10 '10 #8

ADezii
Expert 5K+
P: 8,607
@NeoPa
Don't feel bad, my Senior Moments are the 'Norm' rather that the Exception! (LOL)
May 10 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.