Complicated relationship created on same two tables

[Lou_Reed]

I am trying to create two relations, using two tables. The first one is with
primary key and a foreign key from tblPersonnel to tblTelework. The primary key is in
tblPersonnel with the foreign key in tblTelework. Now I also want to create a second
relationship with the same primary key, but a different foreign key, but the second foreign key


second key is in the same table as the first foreign key.

When I do this now I get a statement that the relation already exists do I want to create another.
I do, but I want it have second foreign key that is in the same table, but not the same foreign key
to a different foreign key.

I am not sure how to do this.

Any help appreciated.

Thanks in advance.

Respectfully,


Lou_Reed

[orange]

How about an example of the business rules involved?

0,1 or Many Personnel can work via Telework ...who mans the office?
A Person works on Telework or AtTheOffice ...can not be both?
A Telework approach lasts for ??? some time period???

Let's get the issue clearly described in English before assigning keys to tables....

[John_G]

I'm not sure that what you want to do makes sense. Are you using the primary key of the first table in the relationships? If so, having two relationships to two different fields in another table (the same one for both) makes no sense.

Having relationships between two fields in Table A to two different fields in Table B is fine, and happens all the time.

What exactly do you mean when you use the term "foreign key"? Can you clarify please?

[Lou_Reed]

Sir, you have a point.

I am not sure how to express it to my supervisor who sketched the diagram that I am using to create the MS Access 2010 db.

I have seen this type of methodology used before. Do a simple internet search (ie. search on one primary key accessing two foreign keys in the same table )
and you will see it has been done before.

However, if I am to explain this to my superiors then I must be detailed. Thus I am requesting a reference for this.

They drew the diagram - not me. So why can't I do it twice instead of only once?

Thanks in advance.

Respectfully,


Lou_Reed


If you look at my posted db you will see that I did this in a primary key table and in one table with two foreign keys.

It only created two new tables *_1 and *_2 when I tried to do it a second time in each table.