Primary Key Index

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

Have 2 tables with the same field that I want to use as the primary/foreign
key. The only problem is when I try to change the field in one of the
tables, I cannot change the index from "Yes (No Duplicates)" to allow
duplicates. The field can have multiple personnel under the same position
number. Any suggestions? Thanks...
 
It sounds like your tables are Positions and Employees in a one-to-many
relationship. The PositionID would be the primary key with no duplicates.
The Employee table would have PositionID as foreign key. Many employees can
hold the same position.
 
Rick

By definition, a primary key field cannot have duplicates. Perhaps the
underlying data model needs a bit more work?

If you are saying one table holds employees and another table holds
employees-at-position, then your data could benefit from a bit more
normalization.

You haven't describe the data/relationships, so the following is just a
guess/idea:

tblEmployee
EmployeeID
FirstName
...

tblPosition
PositionID
PositionTitle
...

trelEmployeePosition
EmployeePositionID
EmployeeID
PositionID
DateOfHire
...

Does this match your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top