Trouble with multiple one-to-one relations on a form

[virtualjm]

I would appreciate any help you could offer. I have a master table with a autonumber primary key called "PermitID". I have two other tables which also have a primary key of "PermitID" (long int), call them supplement1 and supplement2. The idea here is that the master table always exists, but there may be related information in Supplement1 on occasion. Likewise, if Supplement1 exists, there also be further information in Supplement2.

So the two supplement1 and supplement2 are linked to the master with a one-to-one relationship with referential integrity, all through the common "PermitID". On my data input form I have the info from the master table, and two tabbed pages where the supplemental information can be added, if applicable. The query uses two LEFT JOINs, so that I get all the Master records and any supplemental information. This part works fine. But data input is a disaster, and I have no idea why.

First I enter the Master data and it is auto-assigned [PermitID]=78. OK. Next I enter data for Supplement1 and likewise it is assigned [PermitID]=78. Still good. Now I enter Supplement2, and it is also assigned [PermitID]=78. GREAT. Well, not quite. Now I look back at [Supplement1].[PermitID] and it mystically has changed to a big fat zero. I know this because I display the [PermitID}s on the form just so I can peek. What am I missing? BTW, this is Access 2000. One other probably unimportant but interesting aspect is that if I enter Sup2, Sup1, then Master, it works fine. It also works if I enter Master, then Sup2, then Sup1 (which is really weird). Every way except the one I need, which is Master, Sup1, Sup2. Oh, and I could go change that goose-egg back to the correct [PermitID] too, but in pratice I wouldn't even want the [PermitID] fields to show on the form, and shouldn't this happen automatically anyway?

I also created a new database with 3 simple tables with [RecID] & [Masterdata], [RecID] & [Sup1], and [RecID] & [Sup2] fields respectively, where the non-key fields are just text for testing input. I linked these up, created the query and the form. Same result. Can you suggest what my problem might be (be nice)? Thanks heaps.

 

[Steve101]

VirtualJM,

Make sure that you have your joins done correctly. If you have three tables, each with a primary key of f1, and a second data field of f2, then your SQL should look something like this:

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

Note that the join "ON" clause in both cases refers to Table1, the master table.

On your form, the equivalent fields for the other two tables will "auto populate" when you enter a new master record. No need to bind in these fields on the form. Set up and test the query first, BEFORE involving a form.

Hope this helps,
Cheers,
Steve

 

[virtualjm]

Thanks Steve. In my case I am using a LEFT JOIN rather than INNER, but your point about making sure both "ON"s point to Table1.f1 is good. As it ends up, my query was correct. So I still need help - I would be interested to know if this situation can be duplicated. Here is how I set up my test. Three tables, they are

Table1 f1 is autonumber & primary key
f2 is text

Table1 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 f1=f1, which is a one-to-one relationship. Set join type to INNER (for the test, since this has the same problem, I just use INNER joins). Enforce referential integrity.

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

Now create a query exactly as Steve suggested above, where

SELECT Table1.*, table2.f2, Table3.f2
FROM (Table1 INNER JOIN table2 ON Table1.f1 = table2.f1) INNER 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. This is why the error occurs, because the key field of Table2 is now ZERO, which has no related parent in Table1. I have no idea why this behavior occurs, but I do have a two-year-old boy, so it is not an unfamiliar behaviour.

Hmm. I would appreciate any help that could be provided. Kindest regards. Jay

 

[Steve101]

Jay,
This is what I think is going on:

With a one-to-many relationship in a query, providing the "one" side is represented by a primary key, (or other unique index), it will propogate its join field(s) value to the "many" side join. The reverse will NOT happen (ie. you enter the key field in a new record on the many side; the value is propogated back to the "one" side).

The Access engine is treating the one-to-one relationship in exactly the same way as the one-to-many relationship; ie. the table(s) on the right side of the one-to-one join are not propogating their values back when you populate them first in a new record; in any event, because the left table is an autonumber; this situation is problematic.

This is how I think you could deal with the situation:

On your form's Before Update event, make sure that the PermidId field associated with the primary table has a valid number (ie. ensure it has been generated by virtue of another primary table entry having been made; then assign this value to the equivalent fields of the two other tables. This will force equivalence between the keys of the three tables before any records are saved, and hense prevent the problem.

Your other option is to avoid the query alltogether, by using the a main form and two subforms, which each of the three forms being bound to its respective table, and using appropriate parent and child link fields to relate the main to the sub forms.

Hope that this helps,
Cheers,
Steve