To all MS Access experts,

I'm using Ms Access to create a database. I had created some tables. In some tables, there are some fields storing same kind of records.

I wanna ask: how to set the relationship so that when I change a record of a table, another related table will change its record accordingly? Or is there any other method to achieve the same purpose?

thx.

There is data redundancy in your database, why don't put the related record together?

because put it together would be very messy.

try to use "on update cascade" .i forgot where to set already.

enforce referential integrity ?

As both LovesReborn and jakesean mentioned, you need to enable the "Enforce Referential Integrity" feature and its suboption "Cascade update". To do this, go to "Tools" --> "Relationship...". Click and drag the primary key of the primary table to the foreign key of the intermediate table and you will be presented with a dialog box where you can set the two mentioned option above. Make sure you close any open tables before doing this.

oic. is the foreign key of the intermediate table must be also the primary key of that table?

Not necessarily. If a foreign key is also a primary key of the intermediate table, it usually means a one-to-one relationship unless you select two fields from the intermediate table to be primary keys. And, normally, an intermediate table is used to form a many-to-many relationship between the two main tables as foreign keys are fields which its records that can be repeated whereas primary keys are fields which its records must be unique and can't be repeated. Hence, if you join the primary keys to the foreign key of the intermediate table, a one-to-many relationship is formed. This means altogether the relationship between the two main tables is a many-to-many.

if i do this, will the records in a table will be changed accordingly when I changed a record in another table which have the relationship?

thx.

The "Cascade Update" option will only take into account the changes you made to the primary key's data. Let's say you have a main table and you change an initial data of its primary key from "123" to "456". With the "Cascade Update" option enabled, the data of the foreign key in the intermediate table which previously has a value of "123" will be automatically updated to "456". Simply put, it means that whatever changes you made to the primary key's data will in turn apply automatically across all related foreign key's data.

oic.

let said i have two tables with different records but they share the same type of primary key.

how to add new record (record for primary) in both table simultaneously?

for example, I have two tables: Product table and Order table.
Both have same type of primary key: CustomerID
How to do that when I add "Kingston" customer in Product table, another "Kingston" customer automatically add into Order table?