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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
At least one proved solution offered.
Since you want both to work or both to fail, run the action queries within the same transaction.