Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 <-- one-to-many --> tblOne.FieldA
    tblTwo.FieldB <-- one-to-many --> 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

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

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

    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 <-- one-to-many --> tblOne.FieldA
    tblTwo.FieldB <-- one-to-many --> 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 <-- one-to-many--> tblONe

    To establish the relationship, click on BOTH fields in tbleTwo, then drag them over to tblOne.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •