How to Update a Table Using Data From Another Table in Access 2007

Access has a query editor that allows you to update tables using the Structured Query Language (SQL). You can use data located in one Access table to edit the values in a second table. This is accomplished using a "sub-select" statement in the update query. The sub-select statement retrieves data from the external table and sets it to the column you indicate in your update statement.

Instructions

1. Click the Windows "Start" button and select "All Programs." Click "Microsoft Office," then click "Microsoft Access" to open your software. Click the "Open" icon and double-click your Access file to load it into Access.

2. Click the "Queries" button to view a list of your queries. Find the update query you want to edit, right-click it and select "SQL View" to view the SQL syntax.

3. Place the select statement next to the column you want to update. For instance, if you want to update an order number linked to a customer ID, you can use the update query with the sub-select query. The following code updates a customer table using the orders table:

   update customers

   set orderid= (select top 1 orderid from orders o where o.customerid=customers.customerId)

   This statement updates all customers with latest order.

4. Click "Save" at the top of the Access window. To view your changes, double-click the query to execute the code and update your tables.

References

• Tech on the Net: SQL: UPDATE Statement