Access – compare two tables and update or insert data in first table

by

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:

  1. 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.

  2. Double-click on the relationship and choose the join option that includes all records from tblNew and only those that match from
    tblOld.

  3. Select all the fields from tblOld and drag them onto the QBE grid.

  4. For each field, in the Update To cell type in tblNew.FieldName, where FieldName matches the field name of tblOld.

  5. 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.)

  6. 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.

More Related Contents:

  1. How do I use parameters in VBA in the different contexts in Microsoft Access?
  2. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  3. Using Excel VBA to export data to MS Access table
  4. Access-SQL: Inner Join with multiple tables
  5. How to use system username directly in MS Access query?
  6. Query for getting value from another record in same table and filter by difference greater than a gap threshold
  7. Exporting MS Access Forms and Class / Modules Recursively to text files?
  8. How to save the result of a SQL query into a variable in VBA?
  9. Escaping ‘ in Access SQL
  10. SQL Access ” Count “? [closed]
  11. SQL Query output in VBA is different than in SQL Oracle
  12. How to get matching data from another SQL table for two different columns: Inner Join and/or Union?
  13. Case expressions in Access
  14. How do I count unique items in field in Access query?
  15. Date Difference between consecutive rows
  16. Pivoting data in MS Access
  17. coalesce alternative in Access SQL
  18. Why does a LIKE query in Access not return any records?
  19. How to SUM two fields within an SQL query
  20. [] brackets in sql statements
  21. Row numbers in query result using Microsoft Access
  22. can we list all tables in msaccess database using sql?
  23. Use Access SQL to do a grouped ranking
  24. “You tried to execute a query that does not include the specified aggregate function”
  25. How can I implement SQL INTERSECT and MINUS operations in MS Access
  26. SQL: Using NULL values vs. default values
  27. How do I call an Oracle stored procedure from an Excel VBA script?
  28. How do I perform update query with subquery in Access?
  29. SQL Update woes in MS Access – Operation must use an updateable query
  30. How do I create a decimal field in Access with Alter Table?

Leave a Comment