By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,049 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Append and replace field contents in table

P: 6
I have an existing table, I am trying to update the table.
Update existing records with new value and append to it non existing value.
Table 1: item, price
table 2: item, new price
Is there a query to do this. I tried Append and replace but I need to combine both.
Please help
THank you
dré
Oct 25 '06 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,347
I think you're unlucky there - there isn't and 'Replace & Append' query type in Access.

You could always try coding it into two separate queries but you'd probably have to flag any changes so that your second (append) query could recognise the updated records.
Oct 25 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
I don't understand why you are trying to to both at the same time. You can't by the way.

Why not run the update query first.

Then run an insert query to append records not currently existing in the database.

If you have problems designing these queries post the full details and we'll try to help.
Oct 26 '06 #3

P: 6
I don't understand why you are trying to to both at the same time. You can't by the way.

Why not run the update query first.

Then run an insert query to append records not currently existing in the database.

If you have problems designing these queries post the full details and we'll try to help.
I guess you were right. I need to do the following:
First - Update the table
Second - Insert into the table
However I haven't used sql lately in Access. Do I need a procedure inserted into the query...
table1: item, price
Table2: item2, price2
How would the query execute both command ?
Please advice with a sample.....
Thank you.
Dré
Oct 26 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
BACK UP YOUR TABLES ...

It will be two queries. Open the query window in design view and switch to sql view. Don't add any tables just leave it blank. Paste in the first query changing the table names and field names if appropriate.

UPDATE Table2 INNER JOIN Table1 ON Table2.Item=Table1.Item SET Table2.NewPrice=Table1.Price;

Run this query.
This should update all the items in the table. For items not currently in the table paste in the following following instructions as above.

INSERT INTO Table2 ( Item, NewPrice )
SELECT Table1.Item, Table1.Price
FROM Table1 LEFT JOIN Table2 ON Table1.Item = Table2.Item
WHERE (((Table1.Item) Not In ([Table2].[Item])));

MAKE SURE you back up your tables first...
Oct 27 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.