Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Unanswered: Relationship Dialog box wrong table as primary

    Hi

    I have a contacts database with a contacts table and a locality table that will become part of a lookup on a form to populate the locality field in the contacts table. The Contacts table has a field LocalityID that is a long integer and the Locality table has an autonumber LocalityID as it's primary key. It does not matter which way I drag either of these keys I end up with the one to many relationship around the wrong way. In the Edit Relationship dialog box the primary table is set to the Locality table not the Contacts table.

    I have created a second practice table with the same result, what am I doing wrong?

    Thanks
    Di

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sounds like the dialog box is correct. After all the table holding the primary key will be the 'parent' table.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2012
    Posts
    5
    Thanks for the swift reply Healdem.

    Both tables have got auto numbers as primary keys, but my relationship is one suburb to many contacts and I would have thought in reality it should be a one to one relationship, one contact to one locality. Surely the parent table needs to be the contacts table because the form with the locality combo box (linked to the locality table or a qry whichever is preferable) will populate the locality field in the contacts table.

    Di

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry it looks like I didn't make myself clear
    you have two tables, and as you would expect in a relational DB both have primary keys. however when creating this relationship only the primary key of the locality table is included when defining the relationship, so it will be the 'parent' table, with the LocalityID in the contacts table defining it as the 'child' table.

    the ID in Locality is the primary key
    LocalityID in the contacts table is the foreign key.

    if that doesnt' match the business requirement then you will have to remodel your DB.

    Given the table names I suspect you probably want to allow a specific person to have more than one locality ID, rather than at present where persons have one localityID in the contacts table identifying their sole location. if you need to assign multiple localityID's to a single person then you need to look at creating an intersection table which comprises the two (or more) primary keys of the tables involved, plus any other pertinent information (if any) that is relevent to that particular instance.
    Google
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2012
    Posts
    5
    Thanks again, slowly getting my head around it.

    Di

Posting Permissions

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