A primary key with two foriegn keys

[Lou_Reed]

Is it possible for a primary key in a table to have two foreign keys. It seems possible
with some modification. I just do not know.

I have table with the primary key Personnel. Now it has two foreign keys that are in two different tables.




When I developed the relationship with the Personnel table (with a primary key) and a table with a foreign key then things operate as they normally
would. This is the first foreign key.


But when I try to add a second different table but with the same foreign key, it creates a second table something like Personnel_1. In other words it takes the original table (Personnel) with the primary key and then creates a new table like Personnel_1 and uses its primary key to connect to the second foreign key.

Can I just have the original table with a its primary key connected to two foreign keys in two different tables?


Thanks in advance.


Respectfully,


Lou_Reed

[Lou_Reed]

I just want to have this primary key with two foreign keys. But I do not want a second table with a primary key.

Any help appreciated.

Respectfully,


Lou_Reed

[John_G]

Where are you trying to do this - in the relationships tool or in a query?

Your terminology is confusing - when you say "I just want to have this primary key with two foreign keys..." what do you mean? Do you have a table with a primary key (PK) in one field, and two other fields that are foreign keys (FK) to two other tables? That should not pose a problem - I do that all the time. You can also have the same FK field linking to several tables - again, not an issue.

But your description of the problem sounds as if you have two FK fields in one table pointing to the same second table, not two different ones.

Can you clarify things a bit?

[Lou_Reed]

I am doing it because at our last meeting, that was what was agreed on by the people paying me.

I said at the time that I did not know if it could be done. That is the reason for this question on the forum.

I have single table and the primary key of that single table is connected to two different foreign keys
in two different tables.

Now when I set this up and added a second foreign to the primary key of the first table, the primary key table split into two tables:
table and table_1. Each had their primary key and each primary key was attached to two different tables.

I was wondering if I can get rid of that second table that MS Access 2010 created. Can I just use one table with its primary key connected
to two foreign keys.

Any help appreciated.

Respectfully,

Lou_Reed

[ssanfu]

Now when I set this up and added a second foreign to the primary key of the first table, the primary key table split into two tables:
table and table_1. Each had their primary key and each primary key was attached to two different tables.

You are doing something wrong. Usually this happens when there is a design error (but not always).
I have a table that is linked to 5 other tables (PK to FK) and nary a "table_1" in sight.

Can you post a picture of the relationship window?

[Lou_Reed]

I believe that you are correct. There is no other reason for this than the fact that the secondary table, the Table-1 table was merely
a move for space saving. That is all it was.

Thanks for the help!

Respectfully,


Lou_Reed