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.
