I created a query that looks for records in Table2 that do not have a matching value in Specialization. So It looks at a record in Table1, determines the name, looks for that name in Table2, determines if Specialization is different in table two for that name.
Code:
SELECT Table1.id, Table1.EmployeeName, Table1.Specialization
FROM Table1 LEFT JOIN Table2 ON Table1.[Specialization] = Table2.[Specialization]
WHERE (((Table2.Specialization) Is Null));
This query does not consider that there may be duplicate names in either of the tables.
I created a form and some VBA. The VBA use DAO to open the above query and loop through the records. If it finds any records, it will update Table2 with the Specialization from Table1. Everything is considering the NAME and not the Primary Key.
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strEmployeeName As String
Dim strSpecialization As String
Dim strSQL As String
Set db = CurrentDb
Set rs = db.OpenRecordset("qryNamesWithNewSpecialization", dbOpenSnapshot)
'Make sure we have records and then
'make sure we are at the first record
If rs.RecordCount < 1 Then
MsgBox "No records require an update"
Set rs = Nothing
Set db = Nothing
Exit Sub
End If
rs.MoveFirst
'We need to loop through all of the records
'that our query object found
While rs.EOF = False
strEmployeeName = rs![EmployeeName]
strSpecialization = rs![Specialization]
strSQL = "UPDATE Table2 SET Table2.Specialization = '" & strSpecialization & "' WHERE ((Table2.EmployeeName)='" & strEmployeeName & "')"
db.Execute strSQL, dbFailOnError
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox "Complete"