Access 97 - Relationships and Cascading Updates problem

[zill]

Subset of Database Tables/Fields are as follows:
Table1 Field1 and Field2 values can occur multiple times in the table
Table2 Field1 has a unique value in the table
Table3 Field1 and Field2 when combined have a unique value

Table1 Field1 and Field2 not indexed
Table2 Field1 indexed - unique values
Table3 Field1 and Field2 multiple field index - unique values

Relationships defined as follows:
Table1 Field1 many-to-one join to Table2 Field1
Table1 Field1 many-to-one join to Table3 Field1
Table1 Field2 many-to-one join to Table3 Field2
Table2 Field1 one-to-many join to Table3 Field1

Objective is to cascade updates through all three tables but this configuration does not allow Field1 to be updated from any table. Field2 changes cascade correctly from Table3 to Table1.

If I remove the Table1 Field1&2 many-to-one join to Table3 Field1&2 then Field1 does cascade updates correctly from Table2 to Table1 and Table3. However, then I am unable to cascade updates from Table3 Field2 to Table1 Field2.

Any ideas how I can get all fields to cascade updates correctly? TIA

Roger

 

[WildHare]

Not to be picky, but your notation of "Many to One" joins is giving me the heebie-jeebies...

I assume you mean ONE to MANY joins. Your Table2 appears to be on the ONE side of two (2), One-to-Many joins, one each to the other tables.

Table3 is the ONE side of the Many- join to Table1. Therefore a cascaded update of Field1 from T(2) to T(3) should work just fine.

The updated T(3)F(1) should then cascade to T(1)F(1).

Or am I missing something???





If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[zill]

Thanks for the response.
Sorry about the terminology - I am just an "enthusiastic user" rather than an IT professional. i.e. no formal database training!
The problem was caused by the join between T(1)F(1) and T(2)F(1). When this was deleted, changes to T(2)F(1) cascaded correctly to T(3)F(1) and then to T(1)F(1).
The additional join, although logically correct, seemed to cause a conflict within Access, preventing cascading updates.
However, it is apparently working correctly now. Thanks again for the help. Best regards,

Roger