You have this relationship:
People::TeamID = Teams::TeamID
You can add another table occurrence to get:
Teams::CaptianID = People 2::PeopleID
And CaptianID can be formatted as a value list of team members (conditional value list) so that you select the team caption from the group via drop down list of these individuals.
Then you can put the name field from People 2 (or what ever you call this added occurrence of People) to your people layout to show the team captian for each team member.
To make a new table occurrence:
In Manage | Database | relationships, make a new table occurrence of People by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
So as I've worked to understand and implement this, I think I've refined the approach somewhat, so I can understand better the question to be asked. Here's where I am now:
I have People::TeamID>-Teams::TeamID (one team has many people; is >- the correct nomenclature here?). Each team has several roles to be filled, such as Captain, Painter, etc. I was wrong earlier to focus on Captain as a Team identifier, since Captain is just one of the several roles to be filled on each team. My goal is to have people in each of the team roles, and I think the way to do it is first, to assign people to a team, then of those assigned people to put each one in the desired role. I don't know if this can be done in one step or not.
For the first step, assigning people to teams, I generated a Value List called TeamIDs from fields: "Teams::TeamID and Teams::Captain", and then used these as a dropdown list on the People layout to say, for example, Sally Smith is assigned to TeamID=3. Likewise Joe Jones is assigned to TeamID=3. Now when I go to the Team3 layout, I'd like to click on Painter and choose from a dropdown which includes Sally Smith and Joe Jones. But I can't figure out how to construct the value list from which to display the dropdown. If I'm on the Team4 layout, then the value list would contain different names from which to choose.
I've produced a People2 table occurence (Teams::PeopleID>-People2::PeopleID )as you suggest, but I can't figure what to do next. For one thing, the relationship is backward (one person has many teams, instead of the correct relation one team has many people.)
Have I followed your suggestions correctly, or have I veered off in the wrong direction?
Am I on the right track for solution, or am I in the wrong ballpark altogether?
Thank you again for your help.
You started out right with
People::_kf_Team_id = Teams::_kp_team_id
but then I suggested
Teams::CaptainID = People2::_kp_people_id
That produces: People>---Teams-----People2 (>---- stands for many to one)
That's reasonable when we just wanted to identify the captain and is still useful if you want to access a People record and see the name or other info about their team captain.
But now we are assigning each person a different role on the team and you've already indicated that a person is only a member of one team. Assuming that a person can only have one role on the team, I'd add a field to the People table to use for assigning roles.
If there is even the slightest chance that a person could be a member of more than one team, I'd use a join table between Team and People for managing team membership. This might be a useful approach even if this is not possible, but the same person might be assigned more than one role as you can designate roles with a field in the join table.