Article ID: 103990
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 Q103990
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.
STATUS
This behavior is by design.
MORE INFORMATION
If an update query in Microsoft Access is used 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
Create the following three tables:
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
Add the following two records to the Vendors table:
Name ---- Bob Jill
Enter the following three records to the New Parts table:
Item Vendor Cost ----------------------------- fender Bob 100.00 hood Jill 50.00 trunk Bob 100.00
Enter the following four records in the Rebuilt Parts table:
Item Vendor Cost ---------------------------- Pump Bob 10.00 Fan Jill 45.00 Pulley Bob 20.00 Piston Bob 30.00
Create the following query based on the three tables that you created above:
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
- Run the query, and then save and close it.
- 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. - 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, search the Help Index for "update queries," or ask the Microsoft Access 97 Office Assistant.
Additional query words: action
Keywords: kbprb KB103990