Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2014

    Unanswered: Can I set a relationship with 3 different tables that have the same Primary Key

    Hello. I hope I am posting this question in the correct area and manner. I am a new Access 2007 user and am trying to link 3 tables so that I can then create a query. I am creating a relationship between 3 tables using the Primary Key as what they all have in common. The Primary Key for each table is the same and is a unique identifier. It is in fact the employee ID number. It doesn't appear that Access likes this because when I create the query only 49 of the 343 records are returned to me. Can I create a relationship between the tables when each table contains the identical primary key information? The tables have no other unique identifier.

    Thank you in advance for any assistance. I really enjoy your site and am learning a lot just from reading the posts.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Usually in such a case, you have a "master" table that have an Identity column (Primary key) while the other table have a Foreign key column that refers to the PK of the "master" table. This does not prevents the other table from having a PK too, however this PK is not used to create the relationship. For three tables, you can also have a model where Table1 has a PK, Table2 has a PK and a FK that refers to the PK of Table1 and Table3 has a PK and a FK that refers to the PK of Table2, this create a hierarchic model. e.g. Table1 contains the Invoices while Table2 contains the Orders (an invoice can have several orders) and Table3 contains the Items (an order can have several items).
    Have a nice day!

Posting Permissions

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