Joining 3 tables w/referential integrity

[Sunshine331]

I have 3 tables: A, B and C.
tblA contains a PK field "Name" which is joined to tblB and tblC as shown
below... which works fine...

tblA tblB
Name -------> Name (many-to-one, w/referential integrity enabled)

tblA tblC
Name -------> Name (many-to-one, w/referential integrity enabled)

Now, tblB has a PK field "Config" that I wish to join to the Foreign key
"Config" field in tblC with referential integrity enforced, so that if I
change Config in tblB, tblC automatically updates to the correct value. How
do I do this?

 

[Jeff Boyce]

A Primary Key field in TableA must be related to a Foreign Key field in
TableB for there to me a 1:M relationship. Are you saying that there are
multiple records in TableA for each record in TableB? If so, you need to
join from the "one side" in TableB (a PK) to the "many side" in TableA (a
Foreign Key).

It seems like you are saying you have One Name in tblB that can have many
"child" records in tblA, and you have One Name in tblC that can have many
"child" records in tblA, PLUS you have One Config record in tblB that can
have many "child" records in tblC.

I am confused! Could you provide some real-world data as an example?

By the way, Access treats the word "Name" as a reserved word. If you use
that same term in your table definitions, expect both Access and you to get
confused.

By the way, #2 -- if you are using a (person's) Name as a primary key, one
can only hope that you never have two Bill Smiths, or Bob Jones, or Robert
Williams. A "name" is probably not a very good candidate as a primary key
(unless you mean something else by "name").

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have 3 tables: A, B and C.
tblA contains a PK field "Name" which is joined to tblB and tblC as shown
below... which works fine...

tblA tblB
Name -------> Name (many-to-one, w/referential integrity enabled)

tblA tblC
Name -------> Name (many-to-one, w/referential integrity enabled)

Now, tblB has a PK field "Config" that I wish to join to the Foreign key
"Config" field in tblC with referential integrity enforced, so that if I
change Config in tblB, tblC automatically updates to the correct value. How
do I do this?

First off, Name is a bad name for a field. All objects in Access have a Name
property; a table has a Name, a field has a Name, a form has a Name. If you
Name a field Name Access won't know what Name you mean!

That said... I question your logic here. If tblC is related to tblA, but *not*
to tblB, how can you ascertain which record in tblB should be joined to tblC?
I wonder if the actual relationship might better be:

tblA tblB
NewName---> NewName

tblB tblC
Config ------> Config

You would have a Name (NewName in my example) field in tblB that you could
find in a query joining tblB to tblC by Config.



John W. Vinson [MVP]

 

[scubadiver]

Your first half tells me that

1) Each "B" has many "A's"

2) Each "C" also has many "A's"

This tells me that "A" is a junction table for a m:m relationship between
"B" and "C". i.e. Each "B" can have many "C's" and each "C" can have many
"B's"

But now I am lost because I can't see why you should try and join "B" and
"C" directly if they are already through "A".

What is the purpose of your database and what do you want to do?