Cannot join 1:M table into M:M tables

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I recently posted a question about multipe M:M relationship tables.


In one reply, RPW responded with the following:


*************
Although the following is an unlikely table construct, it helps for illustrative purposes.

Imagine three tables; tblNames, tblCity, tblAddress. The I want to use 'junction table' called tblNCA. This last table might have the following fields:

tblNCA
ncaID (PK); autonumber
NameID (FK); long integer
CityID (FK); long integer
AddressID (FK); long integer

With these fields, the table functions very well - there is a unique primary key and the foreign keys link the information from the other tables to the NCA table.

Let's assume that we did not want a single field PK and we are going to combine the (FK) fields into a 'composite PK'. The resultant table might look like this:

tblNCA
NameID (FK) (These 3...
CityID (FK) ...fields combine to make ...
AddressID (FK) ...the 'composite' PK)

This table could also function very well.

I sorta figured that with your line of thinking, you would set up the table this way:

tblNCA
ncaID (Having all...
NameID (FK) ...four of these fields...
CityID (FK) ...combined into a...
AddressID (FK) ...single 'composite PK')


*************


Okay, I now have a follow-up question. I need to splice in a 1:M relationship between tblNCA and a subordinate table (let's call it tblSubordinate).

Before creating the composite key of "ncaID, NameID, CityID, AddressID" I only used ncaID (autonumber) to tblSubordinate.SubID (long integer).

Again, now since having created the composite key, I cannot join tblSubordinate into a 1:M relationship from tblNCA to tblSubordinate.

Any suggestions how to do that?


Thanks,
Tom
 
Hi Tom

Let's label the three examples you pasted below as 1, 2, and 3. In example 1 there is no composite key and you had no trouble creating a relationship between this table and another. Plus, because the PK is autonumber, it will always be unique

In example 2, there is no separate autonumber field because the combination of the three FK's are unique (as a combination)

For example 3, I wanted you to understand (perhaps I didn't make it clear) that using an autonumber field PLUS three FK's is un-necessary because the autonumber is already unique. Adding the other three FK's do not make the PK any more unique - so the question is "Why bother to do it?". Of course you have found out that relating a composite key to another table doesn't work (at least for us newbies)

I don't understand why you want to have a composite PK anyway when an autonumber works so easily (as in exapmle 1)

rp

----- Tom wrote: ----

I recently posted a question about multipe M:M relationship tables


In one reply, RPW responded with the following


************
Although the following is an unlikely table construct, it helps for illustrative purposes.

Imagine three tables; tblNames, tblCity, tblAddress. The I want to use 'junction table' called tblNCA. This last table might have the following fields

tblNC
ncaID (PK); autonumbe
NameID (FK); long intege
CityID (FK); long intege
AddressID (FK); long intege

With these fields, the table functions very well - there is a unique primary key and the foreign keys link the information from the other tables to the NCA table

Let's assume that we did not want a single field PK and we are going to combine the (FK) fields into a 'composite PK'. The resultant table might look like this

tblNC
NameID (FK) (These 3..
CityID (FK) ...fields combine to make ..
AddressID (FK) ...the 'composite' PK

This table could also function very well

I sorta figured that with your line of thinking, you would set up the table this way

tblNC
ncaID (Having all..
NameID (FK) ...four of these fields..
CityID (FK) ...combined into a...
AddressID (FK) ...single 'composite PK')


************


Okay, I now have a follow-up question. I need to splice in a 1:M relationship between tblNCA and a subordinate table (let's call it tblSubordinate).

Before creating the composite key of "ncaID, NameID, CityID, AddressID" I only used ncaID (autonumber) to tblSubordinate.SubID (long integer)

Again, now since having created the composite key, I cannot join tblSubordinate into a 1:M relationship from tblNCA to tblSubordinate

Any suggestions how to do that


Thanks
To
 
RPW:

Thanks for replying to this again...

Wow, now I'm totally confused!?!?


Let's recap for clarification:
1. I was okay 2 a M:M relationship using 2 tables + junction table
2. I then posted thread asking "what if I wanted to use 3 (or more) tables +
junction table
3. TC's and your suggestion was (as far as I understood) to use a "composite
key". I followed your example of the NCA... which worked perfectly in the
example. All keys were created as expected.
4. I now have customized my actual structure to fit the NCA examples. In
my structure, I'm using 5 tables that are all joined in the junction table
via the composite key
5. However, based on an database structure, I needed to insert another table
(e.g. "tblSubordinate"). The tblSubordinate has its Autonumber PK plus a
FK that was used to join its parent table Autonumber PK.
6. Again, I feel like I'm back to square 1.... I liked what you suggested
and I'm not firm to use the composite PK vs. Autonumber PK... it's just that
I think I followed your suggestions and now needed to get additional help.

I hope this makes sense and you see what I'm trying to achieve here. I
have to meet some deadline by tomorrow and hope that you would be kind
enough to provide me more feedback if possible.

Thanks,
Tom





rpw said:
Hi Tom,

Let's label the three examples you pasted below as 1, 2, and 3. In
Click to expand...
example 1 there is no composite key and you had no trouble creating a
relationship between this table and another. Plus, because the PK is
autonumber, it will always be unique.
In example 2, there is no separate autonumber field because the
Click to expand...
combination of the three FK's are unique (as a combination).
For example 3, I wanted you to understand (perhaps I didn't make it clear)
Click to expand...
that using an autonumber field PLUS three FK's is un-necessary because the
autonumber is already unique. Adding the other three FK's do not make the
PK any more unique - so the question is "Why bother to do it?". Of course
you have found out that relating a composite key to another table doesn't
work (at least for us newbies).
I don't understand why you want to have a composite PK anyway when an
Click to expand...
autonumber works so easily (as in exapmle 1).
rpw

----- Tom wrote: -----

I recently posted a question about multipe M:M relationship tables.


In one reply, RPW responded with the following:


*************
Although the following is an unlikely table construct, it helps for illustrative purposes.

Imagine three tables; tblNames, tblCity, tblAddress. The I want to
Click to expand...
use 'junction table' called tblNCA. This last table might have the
following fields:
tblNCA
ncaID (PK); autonumber
NameID (FK); long integer
CityID (FK); long integer
AddressID (FK); long integer

With these fields, the table functions very well - there is a unique
Click to expand...
primary key and the foreign keys link the information from the other tables
to the NCA table.
Let's assume that we did not want a single field PK and we are going
Click to expand...
to combine the (FK) fields into a 'composite PK'. The resultant table might
look like this:
tblNCA
NameID (FK) (These 3...
CityID (FK) ...fields combine to make ...
AddressID (FK) ...the 'composite' PK)

This table could also function very well.

I sorta figured that with your line of thinking, you would set up the table this way:

tblNCA
ncaID (Having all...
NameID (FK) ...four of these fields...
CityID (FK) ...combined into a...
AddressID (FK) ...single 'composite PK')


*************


Okay, I now have a follow-up question. I need to splice in a 1:M
Click to expand...
relationship between tblNCA and a subordinate table (let's call it
tblSubordinate).
Before creating the composite key of "ncaID, NameID, CityID,
Click to expand...
AddressID" I only used ncaID (autonumber) to tblSubordinate.SubID (long
integer).
Again, now since having created the composite key, I cannot join
Click to expand...
tblSubordinate into a 1:M relationship from tblNCA to tblSubordinate.
 
Comments are in-line...

----- Tom wrote: ----

RPW

Thanks for replying to this again..

Wow, now I'm totally confused!?!

Sorry about that....

Let's recap for clarification
1. I was okay 2 a M:M relationship using 2 tables + junction tabl
2. I then posted thread asking "what if I wanted to use 3 (or more) tables
junction tabl
3. TC's and your suggestion was (as far as I understood) to use a "composit
key". I followed your example of the NCA... which worked perfectly in th
example. All keys were created as expected

TC knows what he's doing. As I recall, he did not add an additional Auto# to his example (like you did). The combination of two or three FK's was enough to insure a unique PK. Therefore, he CHOSE to have a composite key in the junction table. There are probably many good reasons for doing so, but I don't know them. Also, Lynn Trapp has provided you alot of details on setting up your tables and none of the examples included composite keys (that I recall)

4. I now have customized my actual structure to fit the NCA examples. I
my structure, I'm using 5 tables that are all joined in the junction tabl
via the composite ke

Which NCA example? The single auto # PK (example 1) should work no problem
Ohhh, you grouped all of the FK's into a composite. (example 3

5. However, based on an database structure, I needed to insert another tabl
(e.g. "tblSubordinate"). The tblSubordinate has its Autonumber PK plus
FK that was used to join its parent table Autonumber PK

Doesn't sound tough... And what is the parent table structure exactly? Is it a single field Autonumber PK, or is it a composite PK with one of the fields being an Autonumber

6. Again, I feel like I'm back to square 1.... I liked what you suggeste
and I'm not firm to use the composite PK vs. Autonumber PK... it's just tha
I think I followed your suggestions and now needed to get additional help

Ok, for now I suggest you stick with the Auto# PK and avoid the composite keys. Here's what I gather so far..

tblJunctio
JuncID ( I suggest an auto # PK here
tblOneI
tblTwoI
tblThreeI
tblFourI
tblFiveI
(and any other fields describing this topic...

tblSubordinat
SubI
JuncI
(and any other fields describing this topic...

In the Relationships window, drag the JuncID from one tblJunc to tblSubordinate and then set your definitions for the relationship you've created. (Of course, you'll have to use your own table and field names for this to work.

I think the reason why you were having trouble with relating tblJunction to tblSubordinate is because you had created a composite key. Not being an expert, I'm going to go out on a limb here and guess at the reasons. I think that you probably dragged only the JuncId to tblSubordinate. Access then bawked at that. I suspect it bawked because you are only dragging a portion of the PK over to the sub table. (Sort of like providing only the middle two digits of your SS# to the taxman.) Did you get a message box saying something to the effect of "No unique index found for the referenced field of the primary table"

When I created some test tables and created all composite key fields from tblJunc in tblSub, there was no problem in creating a relationship. But I have to ask myself: why do I want to do it this way and have all those extra fields??? So I'm back to "Stick with one autonumber PK and have a matching long-integer FK in the related table.

Hope this helps to clarify things for you

rp

I hope this makes sense and you see what I'm trying to achieve here.
have to meet some deadline by tomorrow and hope that you would be kin
enough to provide me more feedback if possible

Thanks
To





rpw said:
Hi Tom
Click to expand...
example 1 there is no composite key and you had no trouble creating a
relationship between this table and another. Plus, because the PK is
autonumber, it will always be unique.that using an autonumber field PLUS three FK's is un-necessary because the
autonumber is already unique. Adding the other three FK's do not make the
PK any more unique - so the question is "Why bother to do it?". Of course
you have found out that relating a composite key to another table doesn't
work (at least for us newbies).
Although the following is an unlikely table construct, it helps for illustrative purposes.
Click to expand...
use 'junction table' called tblNCA. This last table might have the
following fields:
ncaID (PK); autonumber
NameID (FK); long integer
CityID (FK); long integer
AddressID (FK); long integer
Click to expand...
primary key and the foreign keys link the information from the other tables
to the NCA table.to combine the (FK) fields into a 'composite PK'. The resultant table might
look like this:
NameID (FK) (These 3...
CityID (FK) ...fields combine to make ...
AddressID (FK) ...the 'composite' PK)
ncaID (Having all...
NameID (FK) ...four of these fields...
CityID (FK) ...combined into a...
AddressID (FK) ...single 'composite PK')
Click to expand...
relationship between tblNCA and a subordinate table (let's call it
tblSubordinate).AddressID" I only used ncaID (autonumber) to tblSubordinate.SubID (long
integer).
 
----- Tom wrote: ----

I recently posted a question about multipe M:M relationship tables


In one reply, RPW responded with the following


************
Although the following is an unlikely table construct, it helps for illustrative purposes.

Imagine three tables; tblNames, tblCity, tblAddress. The I want to use 'junction table' called tblNCA. This last table might have the following fields

tblNC
ncaID (PK); autonumbe
NameID (FK); long intege
CityID (FK); long intege
AddressID (FK); long intege

With these fields, the table functions very well - there is a unique primary key and the foreign keys link the information from the other tables to the NCA table

Let's assume that we did not want a single field PK and we are going to combine the (FK) fields into a 'composite PK'. The resultant table might look like this

tblNC
NameID (FK) (These 3..
CityID (FK) ...fields combine to make ..
AddressID (FK) ...the 'composite' PK

This table could also function very well

I sorta figured that with your line of thinking, you would set up the table this way

tblNC
ncaID (Having all..
NameID (FK) ...four of these fields..
CityID (FK) ...combined into a...
AddressID (FK) ...single 'composite PK')


************


Okay, I now have a follow-up question. I need to splice in a 1:M relationship between tblNCA and a subordinate table (let's call it tblSubordinate).

Before creating the composite key of "ncaID, NameID, CityID, AddressID" I only used ncaID (autonumber) to tblSubordinate.SubID (long integer)

Again, now since having created the composite key, I cannot join tblSubordinate into a 1:M relationship from tblNCA to tblSubordinate

Any suggestions how to do that


Thanks
To

To answer this question directly

tblSubordinat
SubI
ncaI
NameI
CityI
AddressI

In the relationships window, select all four ID fields in the tblNCA and drag them to tblSubordinate. Relate ncaID to ncaID, NameID to NameID, etc. When you are done, click "Create" and it'll be done

I had to set it up as a test db to make sure it works (actually, this is the first time I've used a composite key) and I must say that I can't imagine why I would ever want to use composite keys. The autonumber PK is just so easy for me to understand, ya know

Anyway, I had posted another response and then re-read this and had a different state of mind/interpreted your question a little differently

Hope all of this helps you

rp
 
Back
Top