ACC2000: Update Query on More Than One Table Gives Wrong Data

ID: Q209229

The information in this article applies to:
Novice: Requires knowledge of the user interface on single-user computers.


SYMPTOMS

An update query that tries to update more than one table may yield unexpected results.


RESOLUTION

You should not update more than one table in an update query.


MORE INFORMATION

If you use an update query in Microsoft Access to update two (or more) tables on the "many" side of a relationship, the resulting updated fields will be multiples of the updated value. Each value will be updated one time for each updated record in the corresponding table.

Steps to Reproduce Behavior

  1. Create the following three tables:


    2. 
   Table: Vendors
   -----------------
   Field Name: Name
      Data Type: Text

   Table: New Parts
   --------------------
   Field Name: Item
      Data Type: Text
   Field Name: Vendor
      Data Type: Text
   Field Name: Cost
      Data Type: Number

   Table: Rebuilt Parts
   --------------------
   Field Name: Item
      Data Type: Text
   Field Name: Vendor
      Data Type: Text
   Field Name: Cost
      Data Type: Number
  2. Add the following two records to the Vendors table:


    3. 
   Name
   ----
   Bob
   Jill
  3. Enter the following three records to the New Parts table:


    4. 
   Item       Vendor       Cost
   -----------------------------
   Fender      Bob        100.00
   Hood        Jill        50.00
   Trunk       Bob        100.00
  4. Enter the following four records in the Rebuilt Parts table:


    5. 
      Item       Vendor      Cost
      ----------------------------
      Pump        Bob        10.00
      Fan         Jill       45.00
      Pulley      Bob        20.00
      Piston      Bob        30.00
  5. Create the following query based on the three tables that you just created:


    6. 
   Query:  Example Query
   ---------------------------------------------------
   Type: Update Query
   Join: [New Parts].[Vendor] <-> [Vendors].[Name]
   Join: [Rebuilt Parts].[Vendor] <-> [Vendors].[Name]

   Field: Name
      Table: Vendors
      Criteria: "Bob"
   Field: Cost
      Table: New Parts
      Update To: [New Parts].[Cost] * 2.0
   Field: Cost
      Table: Rebuilt Parts
      Update To: [Rebuilt Parts].[Cost] * 2.0
  6. Run the query, and then save and close it.


  7. Open the Rebuilt Parts table.

    Note that the costs for items listing Bob as the vendor have been updated to four times their original values, rather than to two times as specified in the update query. Each updated record in the Rebuilt Parts table has been updated once for each updated record in the New Parts table.


  8. Open the New Parts table.

    Note that the costs for items listing Bob as the vendor have been updated to eight times their original values, rather than to two times as specified in the update query. Each updated record in the New Parts table has been updated once for each updated record in the Rebuilt Parts table.


This effect is called a cross product (or Cartesian product), in which the values in Table B are updated one time for each item meeting the same criteria in Table A.


REFERENCES

For more information about update queries, click Microsoft Access Help on the Help menu, type "what is an action query and when would you use one" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: prb action 


Keywords          : kbdta QryMktbl 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: May 13, 1999