Using multiple 1 to 1 relationships

[i herscher]

I have set up a primary table (StudentMain) with two child tables (TermInfo
and SupportDetail) each containing different information ... there is never
more than 1 SupportDetail or 1 TermInfo record for a StudentMain record. A
StudentMain record may have a SupportDetail rec or a TermInfo rec or neither
or both. So the relationships are separate and 1-to-1 and both use FK joined
to the PK in StudentMain with 1-to-1 relationship.

I have a single query which links the 3 tables which doesn't seem to work
consistently when creating new recs in the child tables. For an existing
StudentMain record

.. TermInfo create works but subsequent then SupportDetail fails (related
record required ...)
.. SupportDetail create works but then subsequent TermInfo fails (as above)
.. just to add to the mix, sometimes both work


Can anyone give me a clue where I am going wrong?

 

[Fred]

First if they are never many to one, you might make yur life simpler by
putting them all into one table.

You might try setting those FK fields to be unique just to make sure you
haven't duplicated any FK contents (in which case you have either an error or
a m:1 relationship.

But I'm guessing that your real problem may be in your join types. You
didn't explain exactly what you were trying todo in those queries, but most
likely you be wanting to set both join types to "Show all records in
StudentMain and... "

 

[i herscher]

All this I have already done ... I have just one query which joins the three
tables so that users can browse, update or add records from a single tabbed
form which uses the query as its data source.

I don't want to put fields from the two child tables into the main table
'cos it is big enough already and because not every main record has a child
record.

I set up a test query with the same joins so I could see what was happening
to the keys and discovered that when creating (adding) records to the
separate child tables the following happens to the FKs:

Create TermInfo first (FK is set to StudentMain PK); save record; then go
back to record and create SupportDetail: FAILURE (SupportDetail FK set to
StudentMain PK but TermInfo FK reset to 0 at same time) cannot save record.

Create SupportDetail first (FK set to StudentMain PK); save record; then go
back and create TermInfo: SUCCESS (FK set to StudentMain PK; SupportDetail FK
unchanged)

Create both SupportDetail and TermInfo at same time (without leaving record)
SUCCESS again both FKs set to StudentMain PK.

I have tried a variety of ways of defining the FKs, e.g. having one, both or
neither table using the FK as its own primary key and even setting up one
table with a (dummy) 1-to-many relationship with the main table (in the
latter case none of the 1-to-1 tables fields could have data entered into
them at all).

I think maybe Access does not support being able to update data with two
1-to-1 relationships in a single query - but, I can't see this documented
anywhere (that I can easily find) - If this is the case then I am just going
to have to bite the bullet and combine the fields in one of the tables - but,
it seems a bit of a severe limitation for database software, so if anyone out
there has a better idea .....
-

 

[Fred]

I don't have the answer but a few hopefully useful thoughts.

Regarding capabilities of Access, this is at the rudimentary level. There
is a problem that needs to be found and fixed, not a limitation of the
software.

I find that when you get it narrowed down and it doesn't make sense , it's
time to recheck one's assumptions and observations.

One observation (or deduction from an observation) that you describe is
nearly impossible and thus probably erroneous. That is that you saved a
record in TermInfo and then somehow working with OTHER tables in a view query
modified the contents of a (FK field in) a saved record in TermInfo.

Make sure your experimental query is displaying the fields on both sides of
each join.

If you have "enforce referential integrity" turned on, try turning it off
during your experimentation.

In your second post you didn't describe / confirm your join types. I'd
confirm that both joins are the type that "shows ALL records in StudentMain"
and matching records in the child tables.

If you have further posts, you may want to clarify a few ambiguous areas in
your description. You keep saying just "failure" without saying what the
error message is. And you keep saying "the record" as if it somehow defines
where you are going in an output line of a query which is a compilation of
three records.