Solved

Append or Update Query

Posted on 2014-10-04
3
167 Views
Last Modified: 2014-10-05
I want to run a query that updates a record I a table if the record already exists.   But if the record does not exist I want it to create a new record in the table.

The source table is "Source".  The destination table is "Destination"

The both have a key field named "RecordID"

If I just try to run an update query it fails because the record isn't there.  But if I run an append query it works fine if the record is already there.

Confused!!

--Steve
0
Comment
Question by:SteveL13
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 40361418
this can be done by using a Left join instead of an Inner join in your update query


update source LEFT JOIN destination
ON source.recordid = destination.recordid
SET destination.[field1] = [source].[field1], destination.[field2] = [source].[field2], destination.[field3] = [source].[field3];
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40361494
What Rey said!  (no points please)

Some people call this an UPSERT query.  In SQL Server, they have a similar query syntax called MERGE which gives you the ability to Update and Append at the same time, and you can even get a count of how many of each action are completed.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 40361573
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

809 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