Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/103269

From BetaArchive Wiki
Knowledge Base


ACC: Updating a Table from a Second Table Using an Expression

Article ID: 103269

Article Last Modified on 1/18/2007



APPLIES TO

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q103269

Novice: Requires knowledge of the user interface on single-user computers.


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.

  1. Create a new table with the following fields and save it as Yearly Rainfall:

          City      Inches   Last Updated
          -------------------------------
          Baroda     0.5     4/2/93
          Basildon  22.0     4/2/93
          Beaver    18.0     4/2/93
  2. Create a new table with the following fields and save it as Current Week:

          City      Inches   Week Of
          --------------------------
          Baroda     0.1     4/9/93
          Basildon   2.0     4/9/93
          Beaver     2.5     4/9/93
  3. Create a new query based on the tables Yearly Rainfall and Current Week. Join the tables on the City field.
  4. On the Query menu, click Update.
  5. Drag the Last Updated and Inches fields from the Yearly Rainfall table to the query grid.
  6. Create the following entries in the Update To row of the query grid:

          Field: Last Updated
             Table: Yearly Rainfall
             Update To: [Current Week].[Week Of]
          Field: Inches
             Table: Yearly Rainfall
             Update To: [Yearly Rainfall].[Inches]+[Current Week].[Inches]
  7. Save and then run the query.

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


REFERENCES

For more information about update queries, search the Help Index for "update queries," or ask the Microsoft Access 97 Office Assistant.


Additional query words: total archive history

Keywords: kbinfo KB103269