Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Posts
    5

    Unanswered: how to : create one to one relationship between 3 tables?

    i have 3 tables
    when i create one to one relationship between the main table and one of the tables , seem to work fine with out problem and i can see the + next to the row , when i click it it open the other table linked to it
    but
    when i create another one to one relationship between the main table and the third table , it will not work
    any suggestion

    much appreciated

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Nope
    its not a problem in Access (or at least in A2010)

    its to do with your table/column design
    make certian you have a primary key defined on the desired columns in each table and they are the same datatytpe
    if you are using autonumber then use autonumber in only one table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2011
    Posts
    5

    Than you for the reply

    the main table is tblMain and the columns like below :
    EmCode Number PK
    EmFullName Text
    EmBirthdate Date/time

    second table is tblDriveLicense consist of :
    EmCode Number PK
    LicenceIssuer Text
    City text
    LicenceNo Number


    the third table is tblPassport consist of

    EmCode Number Pk
    PassportNO Number
    EXpireDate Date/time



    when i connect tblMain with tblDriveLicense , by one to one relation between EmCode its ok , but when i connect tblMain with tblPassport also then i got problem , could you do a sample for me and upload it for me much appreciated...

    REgards

  4. #4
    Join Date
    Oct 2011
    Posts
    5

    thank you for your help

    Quote Originally Posted by healdem View Post
    Nope
    its not a problem in Access (or at least in A2010)

    its to do with your table/column design
    make certian you have a primary key defined on the desired columns in each table and they are the same datatytpe
    if you are using autonumber then use autonumber in only one table
    the main table is tblMain and the columns like below :
    EmCode Number PK
    EmFullName Text
    EmBirthdate Date/time

    second table is tblDriveLicense consist of :
    EmCode Number PK
    LicenceIssuer Text
    City text
    LicenceNo Number


    the third table is tblPassport consist of

    EmCode Number Pk
    PassportNO Number
    EXpireDate Date/time



    when i connect tblMain with tblDriveLicense , by one to one relation between EmCode its ok , but when i connect tblMain with tblPassport also then i got problem , could you do a sample for me and upload it for me much appreciated...

    REgards

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What Datatypes are each of the EmCode Number Fields?

    As healdem said, they all need to be the same unless one of them is an Autonumber, in which case the others need to be defined as Number/Long Integer.

    My question, though, is why you are using three Tables, in a one-to-one relationship, to hold a grand total of seven (excluding the PK) Fields? Seems like that has the possibility of creating an awful lot of extra work for yourself.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Oct 2011
    Posts
    5
    They are of the same data type

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If they are of the same data type, then the next step is to post the error message that is displayed when you try to form the relationship.

    Just out of interest, do the tables currently contain any data?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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