title: Reference to composite primary key in Access 2007
body: <p>I have looked around and found some questions similar but they were for SQL Server instead.</p>
  
  <p>Here is a small database structured I have create just to show you the relationships I want to model. Basically it's quite simple, each year has 12 periods and an instance of period-year cannot occur twice (period 9 year 2012 cannot occur more than once ever).</p>
  
  <p><img src="https://i.stack.imgur.com/GqfK3.png" alt="Access composite key database structure"></p>
  
  <p>So I thought that the best way to model this would be to have a table period with only one field with values from 1-12, a table year following the same logic (2011,2012...) and since it is an N-to-N relationship I have created the period_by_year table which joins them to be used by rpt_maintenance_kpi. Now the tricky part is that in order to make each combination unique, I have made the both period_no and year_no part of a composite primary key. This solves the problem elegantly in my opinion, but then I am stuck on how to reference this composite primary key from the rpt_maintenance_kpi (or any other table for that matter). I have tried making two joins but this does not seem to work (creates a second rpt_maintenance_kpi table and I believe this will not do what I want to do).</p>
  
  <p>So how could I handle a foreign key to a composite primary key ?</p>
  
  <p>Many thanks in advance.</p>