MS access 2013 Append to destiation table and set values in source table in one go

Hello, thanks for reading and offering some expert help on my Access question.    Many books, searches and thoughts later I see cannot figure out a way to solve this problem.  I've created a simple DB sample and attached to this question.

Two tables  ITEMS and PROCESSED
rows in ITEMS get marked as 'SOLD'

Append query then looks for rows 'SOLD' and appends data to PROCESSED

(so far so good)

but during the append I'd like to set the row in the source table ITEMS to 'COMPLETE' and 'COMPLETETIMEPSTAMP' set to =now()

(this I cannot figure out)

I have found that using query expressions I can set/change going to the destination table, example I can set the notes field in the destination table.

How can I set a value in the source table during this operation ?

I hope the PDF and sample DB make sense.

Long story short:
Each day the 'SOLD' records are "copied" between two different databases (imagine two departments that use the data in completely different ways).   I want to show in the ITEMS table that the record was 'sent'.
DB-query.pdf
Question.accdb
LVL 1
pc-cytAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Here is how.

First, change the data type of ItemID to Long (done in the attached sample)
Then run the queries when you think new items are ready:

    AppendItems
    ProcessedItems

However, you can run these queries as often as you like as they only process records that hasn't been touched before.

/gustav
Question.accdb
0
 
PatHartmanCommented:
These are two different operations on two different tables and must be done separately.  So - you need both an update query and an append query.

Since you want both to work or both to fail, run the action queries within the same transaction.
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
here see query my_update, run it after the append query
Question_revised.accdb
0
 
Gustav BrockCIOCommented:
At least one proved solution offered.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.