Multiple field primary key

[Robway]

Hope someone can help.

I have a database with 15 tables which revolve around one core table [tbItemNo]. There is a one/many relationship between it and tbAccession in one direction and several others in the opposite direction, eg tbEvent.

tbAccession 1-M tbItem 1-M tbEvent

The primary key in tbItem is an automatic number, but this seems to be preventing me from sorting several of my reports from coming out in the order I want - it sorts by the key field rather than dates (text fields) or alphabetically on other fields. I've tried inumerable combinations in sorting and grouping, but there's always something wrong.

tbItem has a foreign key field to link to tbAccession (AccessionNo) and a field called ItemNo. It is the combination of these two that identify every item in my collection (an archive), and in combination they are unique. Might it help if I made these two a multiple field primary key? Could I do away with the auto number field altogether and link my other tables (eg tbEvent) to the multiple field key? Could this possibly help with my reports?

Another option might be to combine the data from AccessionNo and ItemNo into one field in tbItem, but I'd still need the foreign key field, and that seems to break the normalisation rules.

I'd be grateful for your advice, Robyn

 

[KornGeek]

Yes, it is possible to have a multi-field primary key. Remember that a primary key can never be null. With a multi-field primary key, none of the fields that make up the key can be null.

To set multiple fields as the primary key, open the table in design view. Hold down shift as you select each of the fields. Once they are all selected, right-click one of these fields and select the primary key icon. This will set the combination of the fields to be the primary key.

To link another table to these fields, create a relationship between the tables based on one field. In the relationship window, you will notice additional lines where you can add fields to the relationship. This is how to make the relationship based on multiple fields.

As far as this affecting your reports, that seems unlikely to me. I would recommend creating queries where you can specify the sort order and then base your reports on these queries.

I hope this helps. Good luck.

 

[Robway]

Thanks, that will get me moving.

The reports are all based on multiple table queries already, and I have tried so many different possibilities for grouping etc that I have practically given up in despair - hence this thought that changing the basic structure might have to be the solution. I'll set up a test and see how it works.
Cheers, Robyn