[AccessD] Append/Update tables

Hi John (I mailed this the other day but perhaps it missed the list)

This tip from Smart Access is one of my favourites:

<quote>

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. 

</quote>

Happy New Year to all!

/gustav


>>> joeget at vgernet.net 02-01-2006 15:29:49 >>>
I have a table "tblPA" in a database and have successfully imported the same file from another database.  So now I have "tblPA" and "tblPA1".  The tables have 4 identical fields called "Added Date", "Added Time", "Date Modified", and "Time Modified" plus several others.

I would like to append/update the "tblPA" by adding the new records from the "tblPA1" and append/update the records of "tblPA" if the table "tblPA1" has a later modified date and time.

First, I think I need to delete the records that need to be appended/updated from "tblPA" and then just add the records from "tblPA1" that are new.  That may all be possible by the Date and Time Modified fields and then just using the Added date and time to add the records to "tblPA"

I do believe that that will satisfy the process.  Do you agree? Have an example? Know where there is an example?

Thanks again

John