Microsoft KB Archive/209246

= ACC2000: How to Update a Table from a Second Table by Using an Expression =

Article ID: 209246

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209246



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
This article describes how to create a query that uses an expression to update a record in one table with data from another table.



MORE INFORMATION
The sample query below adds data from the Current Week table to the records in the Yearly Rainfall table.

NOTE: This type of query does not work if the data being added is the result of a totaling or grouping.

To perform the query, follow these steps:  Start Microsoft Access and create a new blank database.  Create a new table with the following fields and data, and then save it as Yearly Rainfall:   Table: Yearly Rainfall

City     Inches   LastUpdated ---  Baroda     0.5     4/2/2000 Basildon 22.0     4/2/2000 Beaver   18.0     4/2/2000   Create a new table with the following fields and data, and then save it as Current Week:   Table:  Current Week

City     Inches   WeekOf --  Baroda     0.1     4/9/2000 Basildon  2.0     4/9/2000 Beaver    2.5     4/9/2000  Create a new query based on the tables Yearly Rainfall and Current Week. Join the tables on the City field. On the Query menu, click Update Query.</li> Drag the Last Updated and Inches fields from the Yearly Rainfall table to the query grid.</li>  Create the following entries in the Update To row of the query grid: <pre class="fixed_text">  Field: Last Updated Table: Yearly Rainfall Update To: [Current Week].[WeekOf] Field: Inches Table: Yearly Rainfall Update To: [Yearly Rainfall].[Inches]+[Current Week].[Inches] </li>  Save the query, and then run it.

The data in the table Yearly Rainfall is now as follows: <pre class="fixed_text">  City      Inches     Last Updated -  Baroda     0.6       4/9/2000 Basildon 24.0       4/9/2000 Beaver   20.5       4/9/2000 </li></ol>

<div class="references_section">