Using Append, Delete and Update Quieries the right way.

John Sheehy
John Sheehy used Ask the Experts™
on
So I currently use an Append Query to copy certain fields data from one table to another.
Then an update query is run to change two fields data from Planned to Ongoing.  

This works well for us if this is the first time it is being run.

I know an append query adds records to a table.  An Update query updates records in table and delete, well deletes records in a Table.

How do I get them all to work together?  Append records from Table1 to Table2 that don't exists in Table2.  Update records in Table2 if the records exists and delete a record from Table2 if they meet certain criteria.

It seems simple enough using the ID of the record that matches in both Table one and two.

Does this make sense or is there an easier way?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Yes, there is a way to do it in one step - as explained here:

Combined "update or Append" query
Distinguished Expert 2017

Commented:
I've done this a number of times but be aware, it only works for Jet/ACE tables.  It doesn't work for SQL Server (or at least it didn't the last time I tried it).
John SheehySystem Security Manager

Author

Commented:
Sorry for the long delay.  Was sick most of last week.  I will try the solution out today and will let you know how it goes.

Thanks
John
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
No problem.
John SheehySystem Security Manager

Author

Commented:
I was about to say this didn't work for me.  But I was looking at it and saw where I added a criteria that wasn't needed  This works great for what I need.

This will also work if the NEW Table is a QUERY and your OLD table is really a table.  I needed criteria from the new table to be specific so I used a query with the criteria set so it only updated what was needed.  

But this worked great.

Thanks.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial