Multiple Primary Keys

[BDawg04]

I made a database that was working fine. It was only after I had finished the database, and data was being entered, that I was informed that the field I designated as the primary key would not work. Origanally, I used 'OffCit' as the single primary key. Now they tell me that one 'OffCit' can be entered several times for different reasons. To solve this, I thought about using autonumbers but have been told, and seen, that this may not be a good idea. So, I am trying to use multiple primary keys. So far I have had no luck. I have attached a screen shot of the tables and relationships. I have 'OffCit', 'AreaofLaw', and 'EffectiveDate' as the primary keys. The same 'OffCit' and 'AreaofLaw' will be used several times in different records. That is the reason for the third primary key. I am wondering if I have set this up right or if there is something I overlooked. Perhaps someone has an idea that may work better. Thanks for any input.

 

[solun]

Well, sounds like there were some process issues... I try to test with real data as I develop and before release so that these kinds of serious problems don't appear so late in the game.

Ask more questions of the client. Maybe they don't understand why you need a primary key? Maybe they don't understand their data? Most users can't give programmers "normalized" data or data structures.

Good luck!

Heidi

Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[BNPMike]

You don't need a primary key although some Access functionality including certain updatable joins might not work if you don't. What you do need is a key. So do your normalisation to identify the key(s). If you don't know how to do that then you need to do some reading.

If it turns out you need three fields to form a key, then declare the three keys together as being primary, but you don't need to do that straight away. SQL allows you to join on any set of join-compatible fields. They don't need to be pre-declared as anything special.