---

MS Access 2003: Update Query that updates values in one table with values from another table

This MSAccess tutorial explains how to create an Update query that updates values in one table with values from another table in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I'm trying to update about 6000+ records from one table with values from another table, but I'm having problems. Actually I am trying to update the Bot table MFG with the Big table MFG when Big.PART = Bot.PART.

The query I'm using is:

UPDATE Bot
SET Bot.MFG = (SELECT Big.MFG FROM Big WHERE Big.PART = Bot.PART)
WHERE EXISTS (SELECT Big.MFG FROM Big WHERE Big.PART = Bot.PART);

Could you please help?

Answer: To update a table with values from another table, you can build your query like the one below:

This query will update the MFG field in the Bot table with the value in the MFG field in the Big table when the PART values match.

The SQL for this query is as follows:

UPDATE Bot INNER JOIN Big ON Bot.PART = Big.PART
SET Bot.MFG = [Big].[MFG];