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.
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).