update only new and records with changes [duplicate]
In my Access datebase I have a two tables:
Table1:
PersNum Name Surname
2321 Lenora Springer
2320 Donya Gugino
3326 Leland Wittmer
4588 Elmer Mcdill
Table2:
PersNum Name Surname
2321 Lenora Farney
2320 Donya Willimas
3326 Leland Wittmer
4588 Maya Mcdill
7785 Yolanda Southall
1477 Hailey Pinner
I need to find a way to check the personal number (field "PersNum"), and then if PersNum exist, update Name and Surname in Table1. If PersNum doesn't exist, insert new row in Table1.
Expected results:
PersNum Name Surname
2321 Lenora Farney (updated surname)
2320 Donya Willimas (updated surname)
3326 Leland Wittmer (without change)
4588 Maya Mcdill (without change)
7785 Yolanda Southall (new person)
1477 Hailey Pinner (new person)
I'm looking for any solutions based on SQL/VBA/DAO/ADO.
One option is an "upsert" or combined append/update query.
This old tip from Smart Access is one of my favourites:
Update and Append Records with One Query
By Alan Biggs
Did you know that you can use an update query in Access to both update and add records at the same time? This is useful if you have two versions of a table, tblOld and tblNew, and you want to integrate the changes from tblNew into tblOld.
Follow these steps:
Create an update query and add the two tables. Join the two tables by dragging the key field of tblNew onto the matching field of tblOld.
Double-click on the relationship and choose the join option that includes all records from tblNew and only those that match from tblOld.
Select all the fields from tblOld and drag them onto the QBE grid.
For each field, in the Update To cell type in tblNew.FieldName, where FieldName matches the field name of tblOld.
Select Query Properties from the View menu and change Unique Records to False. (This switches off the DISTINCTROW option in the SQL view. If you leave this on you'll get only one blank record in your results, but you want one blank record for each new record to be added to tblOld.)
Run the query and you'll see the changes to tblNew are now in tblOld.
This will only add records to tblOld that have been added to tblNew. Records in tblOld that aren't present in tblNew will still remain in tblOld.