My question is pretty simple, yet I am unable to find the answer I need. I have an access database with a table called coaches. I have another table called teams, which can have a coach and assistant coach. I need to be able to allow no duplicates across the “Coach” and “Assistant Coach” Fields. Coaches can be both head coaches or assistant coaches, so I cannot have a coach be both for different teams.
Advertisement
Answer
If a coach can only be assigned to one role for one team, then add the team and role to the coaches table.
This will guarantee that a coach can only be associated with a single team in a single role.