Solved

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

Posted on 2016-08-02
4
22 Views
Last Modified: 2016-08-22
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
0
Comment
Question by:pc-cyt
  • 2
4 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 41739869
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points (awarded by participants)
ID: 41739889
here see query my_update, run it after the append query
Question_revised.accdb
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points (awarded by participants)
ID: 41740294
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41765034
At least one proved solution offered.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now