Access Relationship Table

Question

I would like to set an auto-update relationship between fields in two tables. One table contains information on the team and team leader, while the other table contains the team member name list with team and leader information included. I want the second table updated when the first table is updated (the team name is fixed and the team members always stay in the same team, while the team leader might be changed).

I set the team name as the primary key in the first table, and member name and team name as the primary key in the second table.

I want to use the relationship in Access, but it always shows: no unique index ... for primary table. Any instructions or help with any misunderstanding of the use of relationships is appreciated.

Answer 1

Since the Team Name is fixed, I would suggest creating a Team_ID field in both tables. Give each team an ID (it doesn't really matter what ID they get, so long as you're consistent across both tables) numbered 1 to however many teams you have.

Set the primary key in both tables to be Team_ID.

Set up a 1-to-Many relationship between the two tables, since you can have several team members on each team. Set that relationship up to be Cascading, and it will automatically update the second table when the first table is updated.

Also, I completely agree with ElectricLlama's statement above. Normalize your tables so you're only using each field a minimum number of times. Your first table should have Team_ID, Team_Name and Team_Leader, and your second table should have Team_ID and Team_Member. When you need to see the leader's info, join the two tables on Team_ID and bring in the necessary fields from each table.