Update Query VBA Access

0
votes

I am wanting to copy information from the Id column in the Products table to ProductsId column in another table using vba/sql in Microsoft Access. I was just wondering how I would be able to do this.

I had written an INSERT query but I wasn't sure if this would move the Id from the products table and I need the information in both.

I think an UPDATE query would be best but I'm not too sure on how to write the SET part of the query. The two tables are joined by Client but can I put a WHERE clause into the SET?

1
sqlms-accessvbasql-update

1 Answers

0
votes

If table 2 does not have the corresponding record from table 1 yet, then you need an INSERT query.

INSERT INTO Table2 ( ProductsId, OtherField  )
SELECT ID, Table1.OtherField
FROM Table1;

Otherwise if table2 has the record already, then you need an UPDATE query

UPDATE Table2 
INNER JOIN Table1
ON Table1.Client  = Table2.Client  
SET Table2.ProductsId = Table1.ID;