Data import...??
-
Hi everyone,
got a bit of an issue I hope someone can offer some advice on.
Overview.
Previously I used the attached code to import data into another table using a table (NewPrice List) that contained the new values. When I did this it was an overwrite of existing data , i.e. the itemcode value existed in the table I was trying to overwrite with new values.
Code:
UPDATE MATBIT INNER JOIN [NewPrice List] ON MATBIT.ITEMCODE=[NewPrice List].ITEMCODE SET MATBIT.PRICE = [NewPrice List]!MATBITPRICE, MATBIT.DESC_Col = [NewPrice List]!MATBITDESC_COL;
Now however situatuation is different in that the NewPrice List table contains new values , i.e. not over writting any existing values but creating new ones.
Any offer some advice on how to change the above to allow new values to be inserted.
Many thanks for any advice
-
/*updating existing values*/
UPDATE MATBIT INNER JOIN [NewPrice List] ON MATBIT.ITEMCODE=[NewPrice List].ITEMCODE SET MATBIT.PRICE = [NewPrice List]!MATBITPRICE, MATBIT.DESC_Col = [NewPrice List]!MATBITDESC_COL;
/*inserting new values*/
INSERT INTO MATBIT
(PRICE,DESC_Col,ITEMCODE )
SELECT NoSpaces.MATBITPRICE,...
FROM [NewPrice List] NoSpaces
WHERE NOT EXISTS
(SELECT * FROM MATBIT WHERE MATBIT.ITEMCODE=NoSpaces.ITEMCODE)
-
Jo
Many thanks for this , when I try to run the code from /*inserting new values*/ I get an error.
Syntax error. (Missing operator) in query expression '...'.
Trying to run this is Access at the moment , don't know if that has a change on the code needed ?
Thanks for your help.
-
El,
The ... was "and so on". The left join causes MATBIT.ITEMCODE to be null if NoSpaces.ITEMCODE isn't found in MATBIT. Actual syntax can differ bases on Access version and how you connect to your database (linked tables, oledb,odbc)
INSERT INTO MATBIT
(PRICE,DESC_Col,ITEMCODE )
SELECT NoSpaces.MATBITPRICE,NoSpaces.MATBITDESC_COL,NoSpaces.ITEMCODE
FROM [NewPrice List] NoSpaces
LEFT JOIN MATBIT
ON MATBIT.ITEMCODE=NoSpaces.ITEMCODE
WHERE MATBIT.ITEMCODE IS NULL
-
Jo
Many thanks for your help , that has worked a treat. Now have to understand fully what is going on.
Many thanks once again
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply