---

MS Access 2007: 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 2007 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2007, 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: First, you need to create a new query. To do this, select the Create tab in the toolbar at the top of the screen. Then click on the Query Design button in the Other group.

When the Show Table window appears, select the tables that you wish to use in the query and click on the Add button. When you have finished adding your tables, click on the Close button.

In this example, we've selected the Big and Bot tables. You can select multiple tables by holding down the Ctrl key while selecting the table names.

Next, right-click somewhere next to the tables (but not on a table) in the query editor, and select Query Type > Update Query from the popup menu.

Next, build the 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 Big INNER JOIN Bot ON Big.PART = Bot.PART
SET Bot.MFG = [Big].[MFG];