Automatically update tables when other table changed 1

[Guest_imported]

hi
i am trying to create tables for mechanical parts in a project, each table refers to different area project and its parts.
however, i will have about 20 tables and all of these tables have the productID as a primary key.
what i want exactly is that when i change the productID in any table it will be updated in other tables and i dont have to go to each table to update the productID.
Also, each table has a field called Quantity which is the number available for a pecific product, i want this field to be adjusted automatically as well in other tables when i add or withdraw parts.

thanks

 

[tlbroadbent]

Look on the Tools menu. Select Relationships. You'll get a Relationships Window. Add all of the tables with ProductID to the window.

Establish the relationship between the tables by dragging the ProdictID from the Primary table to the ProductID column on each of the other tables. Note: Access may automatically do this for you.

Double-Click on each Relationship line to open the Edit Relationships dialog. Make sure the relationship is listed correctly. Check on Enforce Referential Integrity. Then Check on Cascade Update Related fields.

Exit and Save the Relationships. Now changes to the Primary table ProductID will cascade through all related tables. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Guest_imported]

thank u very much

 

[JerryDennison]

You're structure is wrong. There is no need to have a different table for each part. This will not only cause you many headaches when designing forms (you'll need a form for each table {I know it can be done with code but it's still a major pain}) but the same holds true for your queries and reports. You need to normalize the data.