Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-29-2014, 09:24 PM   #1
martinr
Newly Registered User
 
Join Date: Nov 2011
Posts: 58
Thanks: 26
Thanked 3 Times in 3 Posts
martinr is on a distinguished road
updating/adding new records to existing tables

I have a table with more than 60 fields and need to update it with records from another Access file with a table with an identical data structure.

Is it better to run an update query or an append query (i would have to delete the original records in the target table first) or a union query?

If I run an update query I will have to manually add each field to the query.

the update query will not add 'new' records.
If I run an append it is quicker because I can use the * to match all fields, but i will have to delete the 'old' records first, as both tables use autonumber for the PK so the PK ID will be the same in each table (will get a key violation error).

If I import the 'new' table and run a union query it will match the fields and add the new records, but then i will have to create new table from that query.

is there a better way to do this?

martinr is offline   Reply With Quote
Old 07-29-2014, 10:45 PM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,283
Thanks: 78
Thanked 1,409 Times in 1,329 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: updating/adding new records to existing tables

Join the old and new tables on the key field/s in an update query.

Change the join to an outer join with the arrow going from the new data table to the old data table.

It will update existing records and append the new ones.
Galaxiom is offline   Reply With Quote
Reply

Tags
append , query , update

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding tables to existing database zbird Tables 3 01-30-2012 02:49 AM
Adding addition records without overwriting existing records ozzy68 Forms 2 01-18-2012 08:21 AM
DAO adding 1 new field to 2 existing tables JonAccess Modules & VBA 13 09-15-2011 09:02 AM
Updating Existing Records china99boy Forms 3 06-27-2007 12:33 PM
Updating existing records using forms Sean Forms 2 01-22-2001 10:44 AM




All times are GMT -8. The time now is 06:19 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World