Primary Key Index

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

 

[KARL DEWEY]

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.

 

[Jeff Boyce]

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