Version 1.1           Last Updated 18 Sept 2018                 Approx 0.5 MB


A very common database task is to both append new records to a table and update existing records.
The standard approach is to use an APPEND query together with an UPDATE query.
However, this article explains how you can combine both the APPEND & UPDATE into one query.

For example, you could do this if you have two versions of a table, tblOld and tblNew, and you want to integrate the changes from tblOld into tblNew.

Various suggestions have been made for naming this technique including UPAPP / UPEND / APPDATE / APPUP and most commonly UPSERT
I prefer to call it UPEND!

The technique as originally described many years ago by Alan Biggs (Smart Access)is as follows:

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 tblOld and only those that match from tblNew.

3.   Select all the fields from tblNew and drag them onto the query design grid.

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

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

6.   Run the query and you'll see the changes to tblOld are now in tblNew.

This will only synchronise records in tblNew with those in tblOld.
Any records in tblNew that aren't present in tblOld will still remain in tblNew.

NOTE:
This whole process can be summarised much more simply as:
1.   Create a standard UPDATE query
2.   Change it from an INNER join to a OUTER join from tblOld to tblNew

The attached example database contains:
a)   2 tables - tblOld with 12 records & tblNew with 6 incomplete records


b)   Query qryAppendNew - adds new records from tblOld to tblNew but doesn't update existing records


      The query SQL is:

INSERT INTO tblNew ( ID, StartDate, EndDate, NumberField )
SELECT tblOld.ID, tblOld.StartDate, tblOld.EndDate, tblOld.NumberField
FROM tblOld LEFT JOIN tblNew ON tblOld.ID = tblNew.ID
WHERE (((tblNew.ID) Is Null));

c)   Query qryUpdate - updates records in tblNew but doesn't add any new records


      The query SQL is:

UPDATE tblNew INNER JOIN tblOld ON tblNew.ID = tblOld.ID
SET tblNew.StartDate = [tblOld].[StartDate], tblNew.EndDate = [tblOld].[EndDate], tblNew.NumberField = [tblOld].[NumberField];

d)   Query qryUpEnd - combines both in one query - adds new records AND updates existing records


      The query SQL is:

UPDATE tblNew RIGHT JOIN tblOld ON tblNew.ID = tblOld.ID
SET tblNew.StartDate = [tblOld].[StartDate], tblNew.EndDate = [tblOld].[EndDate], tblNew.NumberField = [tblOld].[NumberField];

      After running qryUpend, tblNew will contain the same data as tblOld



Download

      Click to download:       Upend Query Example            Approx 0.5 MB (zipped)



Feedback

      Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

      Do let me know if you find any bugs in the application.

      Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington                       Mendip Data Systems                       Last Updated 18 Sept 2018

Return to Code Samples Page

Return to Top