INF: Creating Queries to Execute Cascading Updates

PSS ID Number: Q100137
Article last modified on 08-22-1993

1.00 1.10
WINDOWS

---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Access versions 1.0 and 1.1
---------------------------------------------------------------------

SUMMARY
=======
This article demonstrates how to create a query that automatically
updates the many side of a one-to-many relationship when you change a
value on the one side.
NOTE: You can do this only if referential integrity is not enforced
in the relationship.

MORE INFORMATION
================
In the sample database NWIND.MDB, there is a one-to-many relationship
between the Categories and Products tables. The following steps
explain how to create a query that automatically updates information
in the Products table (many side) whenever you change a value in the
Categories table (one side).
1. Open NWIND.MDB.
2. Make a copy of the Categories table and name it Categories Test.
   Make a copy of the Products table and name it Products Test.
3. From the Edit menu, choose Relationships. Establish a one-to-many
   relationship between Categories Test and Products Test on the
   Category ID field. Do not enforce referential integrity.
4. Create a new query based on the Categories Test and the Products
   Test tables. From the View menu, choose Table Names.
5. Drag the Category ID field from both the Categories Test and
   Products Test tables to the QBE grid.
6. From the Query menu, choose Update. Design your query as follows:
      Query: UpdateManySide
      ----------------------
      FieldName: Category ID
         Table: Categories Test
         Update To: [Enter New Value]
         Criteria: [Enter Old Value]
      FieldName: Category ID
         Table: Products Test
         Update To: [Enter New Value]
         Criteria: [Enter Old Value]
7. From the Query menu, choose Run.
8. In the Enter New Value box, type "FISH" and choose OK. In the Enter
   Old Value box, type "SEAF". Note that Microsoft Access tells you
   how many records will be updated. Choose OK and save the query.
9. Open the Categories Test and Products Test tables. Note that "SEAF"
   has been changed to "FISH" on both the one side (Categories Test)
   and the many side (Products Test) of the one-to-many relationship.

Additional reference words: 1.00 1.10 queries one-to-many relationship
KBCategory:
KBSubcategory: QryMaktbl GnrlNw
Copyright Microsoft Corporation 1993.