icarey
asked on
update and insert records
Hello.
I would like to be able to update existing records and insert new records into a main table from another table.
Is this possible using append query to do the inserts and update query to change the data on existing records?
I have been trying to do an append query to append new records into the main table if they don't already exist another table. Then doing an update query to update the existing data in the main table?
How may this be done or is it better doing it with a macro or code
Thanks,
Ivan
I would like to be able to update existing records and insert new records into a main table from another table.
Is this possible using append query to do the inserts and update query to change the data on existing records?
I have been trying to do an append query to append new records into the main table if they don't already exist another table. Then doing an update query to update the existing data in the main table?
How may this be done or is it better doing it with a macro or code
Thanks,
Ivan
ASKER
thanks angelIII
I have created the query ok but am unable to insert into the table due to field count
Tables STOCK and stock_new
Field names in both
NAME TITLE NAME2
SELECT stock_new.*
FROM stock_new LEFT JOIN STOCK ON stock_new.NAME2 = STOCK.NAME2
WHERE (((STOCK.NAME2) Is Null));
displays the new records
INSERT INTO STOCK(NAME,TITLE,NAME2)
SELECT stock_new.*
FROM stock_new LEFT JOIN STOCK ON stock_new.NAME2 = STOCK.NAME2
WHERE (((STOCK.NAME2) Is Null));
comes up with an error
Number of query values and destination fields are not the same
I have created the query ok but am unable to insert into the table due to field count
Tables STOCK and stock_new
Field names in both
NAME TITLE NAME2
SELECT stock_new.*
FROM stock_new LEFT JOIN STOCK ON stock_new.NAME2 = STOCK.NAME2
WHERE (((STOCK.NAME2) Is Null));
displays the new records
INSERT INTO STOCK(NAME,TITLE,NAME2)
SELECT stock_new.*
FROM stock_new LEFT JOIN STOCK ON stock_new.NAME2 = STOCK.NAME2
WHERE (((STOCK.NAME2) Is Null));
comes up with an error
Number of query values and destination fields are not the same
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you angelIII your answer has help me greatly
Ivan
Ivan
first, the update based on the join for the existing ones, and then insert those that are not yet in the table.
the update should be easy.
the insert requires a select like this one:
Open in new window