Problem with multiple one-to-one relations

[virtualjm]

I have posted this question on the "Access - other" forum, but am trying it here also to see if I might find somebody who has seen this before. I am stumped.

I have a "master" table with a primary key. There are two related tables with the same pk. If I create a query to get all records from the master and any in the one-to-one related files it works as expected, but something strange happens on data input. Here is a condensed version of the scenario.

Table1 f1 is autonumber & primary key
f2 is text

Table2 f1 is long & primary key
f2 is text

Table3 f1 is long & primary key
f2 is text

Using Access 2000 relations, set Table1 related to Table2 on Table1.f1=Table2.f1, which is a one-to-one relationship. Set join type to LEFT JOIN. Enforce referential integrity.

Set Table1 related to Table 3 on Table1.f1=Table3.f1, which is also one-to-one and join type to LEFT. Enforce referential integrity.

Now create a query as:

SELECT Table1.*, table2.f2, Table3.f2
FROM (Table1 LEFT JOIN table2 ON Table1.f1 = table2.f1) LEFT JOIN Table3 ON Table1.f1 = Table3.f1;

Now run the query. Enter Table1.f2, Table2.f2 and Table3.f2. Try to save. Error message is "You cannot add or change a record because a related record is required in Table "Table1". It doesn't work. BUT if you enter the fields Table2.f2 and Table3.f2 first, then Table1.f2, it saves with no problem.

By including the key fields, Table2.f1 and Table3.f1 in the query, I can see that if I enter Table1.f2, Table2.f2 and then as soon as I enter Table3.f2, the key field Table2.f1 changes to a ZERO. (huh?) This is why the error occurs, because the key field of Table2 is now ZERO, which has no related parent in Table1 and therefore violates referential integrity.

I have duplicated the problem by creating the three simple tables exactly as described above and the result is the same - If I enter data in Table1 first, which autonumbers the key field, I can then enter info into either related table with no problem. But when I try to enter into the second related table the pk of the first related table changes to a zero. Any help would be appreciated.

Oh, and I have tried various changes to the SQL of the query - including changing it to all INNER JOINS, just for a test, and the result is the same. The problem seems to lies to having two one-to-one relations hanging off the pk of the "master" table. Any help would be greatly appreciated. I hate to resort to using code to populate pk when the referential integrity should be insuring this. Thanks for reading this far! Regards.

 

[LittleSmudge]

One has to ask the all powerful and obvious ( to me ) question :-

Why the $%^&* are you using THREE tables when one will be perfectly adequate?


But back to your original problem:-
I too made up the same demo as you discribe and the only way I could manage to get it to react in the way you have said is to set the
DEFAULT VALUE in Table2.f1 = 0
DEFAULT VALUE in Table3.f1 = 0

Now if you've done that then you should be surprised that it is doing what you've asked it to do!!!

Solution:
Set Default Values to blank - then it works regardless of what order you enter the text in.


'ope-that-'elps

G LS
( ohh for a computer that does what I want it to do - rather than what I tell it to do. )

 

[virtualjm]

Sometimes you spend time looking under the rock and forget to look on top. Thanks Smudge. My wife may feel the sting of having competition in pointing out my gross inadequacies (And, as she points out, it doesn't take keen observation). I guess when they say "default" they aren't just blowing smoke, heh? And why three tables? This is a form, and the info in the main table is always required. One supplemental form is extensive, but only requried in about 10% of the cases and is seldom useful in normal processing, reports, etc so I didn't want to carry that overhead. The second is always required, but may actually be one of three different supplements. So each main form is linked to JUST ONE supplement, but that may be any one of the three. The choice of design is based on the fact that 90% of the time we are just asking questions based on the contents of the "master table" - regardless of the supplemental information. The supplements come into play mostly when putting data in and printing out the forms.

Anyway, thanks again.

 

[LittleSmudge]

Okay Jim, I suspected as much when I asked the question - but it is always good to DOCUMENT the reson to justify unusual data constructs.

Pass on my regards to your good lady.


G LS