Secondary Keys in Access

  • Thread starter Thread starter Paul Norrod
  • Start date Start date
P

Paul Norrod

I am trying to create a simple appliance store inventory
system (for a class I teach) in which I have an inventory
table that uses a composite key of manufacturer ID (6
digits text) and Model ID (10 digits text) (since a model
num could potentially be duplicated between
manufacturers, I use both fields as the primary key).
The problem with that key combination is that I want to
relate it (one to many)to a feature ID table that uses
the inventory key (manu and model) and a feature key as
its primary key. I can't however, create relationships
for both the manu and model.

This lead me to think about making the primary key of
inventory a numeric field and make manu and model a
secondary key. That way, I can relate the inventory
record to my feature table via the numeric inventory ID.

The problem with that idea, however, is that I do not
know how to create a secondary key in Access.

Any info you can provide would be appreciated! Paul
Norrod
 
You can create a relationship consisting of more than one field, however I
like your second approach and is the one I'd use.

Add an autonumber as the primary key in the Inventory table. Open the index
dialog and create an index with the two fields.

eg. Index Name - ManuModel, ManufacturerID as the first field, and then
below it ModelID as the second field. With the first row of the index
selected, set the Unique property to 'yes'.
 
Joan, Thank you very much. Now I understand! I actually
made it work both ways - but you are correct in that
using an autonumber primary key is better. Paul Norrod
 
Back
Top