[AccessD] Append Update records process

Hi John

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 31-12-2005 00:47:39 >>>
Anyone

I have a table "tblPA" in a database and have 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".

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.

If anyone has an example of this and is willing to share it, I would appreciate this.  Or if someone can point me in the correct direction, it would be appreciated also.

Thanks again

John