Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Update a Table from a Second Table by Using an Expression

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

View products that this article applies to.

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).

↑ Back to the top

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.

↑ Back to the top

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:
  1. Start Microsoft Access and create a new blank database.
  2. 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
  3. 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
  4. Create a new query based on the tables Yearly Rainfall and Current Week. Join the tables on the City field.
  5. On the Query menu, click Update Query.
  6. Drag the Last Updated and Inches fields from the Yearly Rainfall table to the query grid.
  7. Create the following entries in the Update To row of the query grid:
       Field: Last Updated
      Table: Yearly Rainfall
      Update To: [Current Week].[WeekOf]
   Field: Inches
      Table: Yearly Rainfall
      Update To: [Yearly Rainfall].[Inches]+[Current Week].[Inches]
  8. Save the query, and then run it.

    The data in the table Yearly Rainfall is now as follows:
       City      Inches     Last Updated
   ---------------------------------
   Baroda     0.6       4/9/2000
   Basildon  24.0       4/9/2000
   Beaver    20.5       4/9/2000

↑ Back to the top

References

For more information about update queries, click Microsoft Access Help on the Help menu, type troubleshoot update queries in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

↑ Back to the top

Keywords: KB209246, kbhowto

↑ Back to the top

Article Info
Article ID : 209246
Revision : 2
Created on : 6/29/2004
Published on : 6/29/2004
Exists online : False
Views : 272