Cannot join 1:M table into M:M tables

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).

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 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

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" wrote in message
...
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).

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
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

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 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.

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. In
my structure, I'm using 5 tables that are all joined in the junction table
via the composite key

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 table
(e.g. "tblSubordinate"). The tblSubordinate has its Autonumber PK plus a
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 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.

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

tblJunction
JuncID ( I suggest an auto # PK here)
tblOneID
tblTwoID
tblThreeID
tblFourID
tblFiveID
(and any other fields describing this topic...)

tblSubordinate
SubID
JuncID
(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.

rpw


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" wrote in message
...
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).
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
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

----- 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:

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

To answer this question directly:

tblSubordinate
SubID
ncaID
NameID
CityID
AddressID

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.

rpw