Relationship headaches w/multiple-field PK (97/SR-2)

Topic

New Reply

Hi all,

I’m stumped as to why I can’t create a relationship between 2 tables which have multiple-field primary keys.

Let’s say I have 2 tables with the following fields:

tblOne:
FieldA
FieldB
FieldC

tblTwo:
FieldA
FieldB

In tblOne, all 3 of its fields comprise the primary key and in tblTwo, all 2 of its fields comprise the primary key.

Now I want to set up the following relationship:
tblTwo.FieldA tblOne.FieldA
tblTwo.FieldB tblOne.FieldB

I can’t do it! Keep getting the error message “No unique index found for referenced field of primary table”. I did look at the Microsoft KB article: http://support.microsoft.com/default.aspx?…b;EN-US;q155514. Came close to getting the answer, but no cigar. Also, in their example, both tables had 2-field primary keys. In my case, one table has a 3-field PK and the other one has a 2-field PK.

What am I doing wrong?

Thanks,

Stephan

Reply | Quote

Replies

Stephan: A couple of notes.
(1) I was able to duplicate your problem.
(2) When you set a relationship between two primary keys, you will get a one-to-one relationship.
(3) When you try to set a relationship between 3 keys and two key, you will be asked to enter a third key on the right.
(4) Return to db design mode and try using a single key for each of the tables. Remember, if you want a one-to-many relationship, a primary key of the main table has to be a foreign key in the secondary table. HTH.

Reply | Quote

Bill,

Thanks for your reply. However, it turns out what I was trying to do is possible. Incredibly, the trick is simply to create the relationship FROM the 2-key table TO the 3-key table. If you try to do it the other way, Access thinks it’s an indeterminate relationship!

Thanks again,

Stephan

Reply | Quote

The Primary Key of your tblOne is irrelevant; it doesn’t even need a PK to establish it as the many side of an one-to-many relationship with tblTwo. I’m not entirely sure how you set-up the relationships. One thing that disturbs me is the way you presented the information:
tblTwo.FieldA tblOne.FieldA
tblTwo.FieldB tblOne.FieldB

This seems to indicated you feel there is a relationship between each field and the same field in the other table. In fact, there is a relationship between the tables; your representation should be:
tblTwo tblONe

To establish the relationship, click on BOTH fields in tbleTwo, then drag them over to tblOne.

Reply | Quote