Create a one to many relationship between 2 tables which have composite primary keys

Question

How can I form a many to one relationship between below tables (C2_Code and C3_Code) which both has composite primary keys ? Or can I ?

Image of the tables to be linked

Answer 1

In Access the process to set up a 1 to many relationship between tables with composite keys is drag and drop just like with normal primary keys, but there is a pitfall. Consider tables 3 & 4 both with composite primary keys.

Note Table 4 has 2 foreign key columns (A and B) in addition to the composite primary key (C and D).

directions for creating a composite primary key: https://www.techwalla.com/articles/creating-composite-keys-tutorial-for-ms-access

To create a composite primary key, remove any current primary key then use shift-click to select the fields for your composite key. The pitfall is you must use primary key in the ribbon rather than right click to set your fields as primary keys. Both A and B will be marked with the key symbol when you are done.

for the last step go back to the relationships tool shift-click columns A & B in Table 3 and drag them to Table 4 and release. Then select the usual options and you are done.