Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Code Samples for Businesses, Schools & Personal Use

Updated 18/09/2018              

 

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 and an UPDATE query.

However, this article explains how to combine both APPEND & UPDATE into one query

 

For example, 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 UPEND!

 

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

 

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.

 

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

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

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

 

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

The 'UPEND' Query

Return to Code Samples Page