Composite primary key reference in Access 2007

Question

I looked through and found some similar questions, but they were for SQL Server.

Here is a small structured database that I created to show you the relationships that I want to model. It is basically quite simple, each year has 12 periods, and the example of a period-year cannot happen twice (the period of the 9th year of 2012 cannot happen more than once).

Therefore, 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 tabular year following the same logic (2011,2012 ...), and since this is an N-to- N, I created a period_by_year table that joins them to use rpt_maintenance_kpi. Now the tricky part is that in order for each combination to be unique, I made the period_no and year_no part of the composite primary key. In my opinion, this solves the problem elegantly, but then I went in cycles on how to refer to this composite primary key from rpt_maintenance_kpi (or any other table, for that matter). I tried to make two connections, but this does not seem to work (creates a second rpt_maintenance_kpi table, and I believe that this will not do what I want to do).

So how can I handle a foreign key for a composite primary key?

Thank you very much in advance.

Answer 1

Create a Year or Period relationship with Maintenance, then double-click the relationship line to edit the relationship, or drag the second part (year or period, respectively) onto Maintenance and select Yes when you are asked if you want to edit the relationship. Now you can add a second line, for example:

Answer 2

Component keys, of course, are useful and solve your problems, but if you really want to avoid combining compound keys, another alternative way to accomplish what you need is to slightly redesign your table structure. Since the final reports are always searched based on the combination of the month and year, this would help to have the MthYear dimension (or table). The following are the entries in this table - with the yymm format:

1301 1302 1303 ... ... ... 1312 1401 1402 ... ... etc ...

You can have additional attributes in the same table in a more descriptive form, for example, the second field can be Jan-13, Feb-13 .... etc.

The MthYear field can be your primary field that you can connect to MthYear in the report table. This will not only allow you to use composite primary keys, but also help you filter only a year or month if you need to use wildcards in your queries or reports. Hope this is helpful for you ... Arvind