4

I have looked around and found some questions similar but they were for SQL Server instead.

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

Access composite key database structure

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

So how could I handle a foreign key to a composite primary key ?

Many thanks in advance.

ApplePie
  • 8,814
  • 5
  • 39
  • 60

2 Answers2

4

Create the Year or Period relationship with Maintenance, then either double-click the relationship line to edit the relationship or drag the second part (year or period accordingly) to Maintenance and choose Yes when asked if you want to edit the relationship. You can now add the second line, like so:

Relationship with two fields

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Thanks for the reply. I did try that before asking my question. For some reason it worked at first. Then I wanted to show it to my team leader so I just undid the joins and redid it in front of him and now it won't do it (and it never worked on the sample db I built at home). I get an error that says "definition of field pby_period_no invalid in the definition of the index or join" (had to translate it from French but it should be helpful). – ApplePie Jul 31 '12 at 10:53
  • All fields are set to 'numeric' in all tables except for id_rpt_maintenance which is 'automatic number'. – ApplePie Jul 31 '12 at 11:08
  • Wait. I managed to make it work. For some reason if there is already a join between the table with composite primary key and the table with the composite foreign keys then it will not work. All I had to do was undo my join, click 'create' then redo both joins in one time. – ApplePie Jul 31 '12 at 11:11
  • Actually I'm designing a new database from scratch because the guy that built the original database apparently did not know or care about database normalization and I had to work with 31 unjoined tables. Normalizing the database made it go from 31 tables to about 15. Also, he had to use quite a bit of VBA to hack it together. Meh. – ApplePie Jul 31 '12 at 11:15
  • Alexandre, I have removed your edit because it is only true in your case, not in all cases. I tested quite a bit and added and removed relationships to get the wording and the image without any problems. – Fionnuala Jul 31 '12 at 11:38
1

Composite keys are of course helpful and solve your problems, but if you really want to avoid composite key joins, another alternate way of accomplishing what you require is to slightly re-design your table structure. As end reports are always sought based on month & year combination, it would help to have a MthYear dimension (or table). The following can be the entries in that table - with a yymm format:

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

You can have further attributes in the same table in more descriptive form like second field could be Jan-13, Feb-13....etc.

The MthYear field can be your primary field which can be connected to MthYear in your report maint table. This will not only avoid using composite primary keys but also help you to filter only year or month if you require using wildcard characters in your queries or reports. Hope this is helpful to you......Arvind

user3113941
  • 11
  • 1