I'm having problems with a relationship.
I have this entities
Matches: MatchIDpk, HomeTeam, AwayTeam
Teams: TeamIDpk, TeamName
Players: PlayerIDpk, TeamIDfk, PlayerName, PlayerPosition
Events: MatchIDfk, EventType, Timestamp, PlayerName, PlayerPosition
Matches are soccer matches that have several events (notable plays that are timestamped)
Teams are soccer teams integrated by Players. Each Match has a Home Team and an Away Team
Players have names and position and are related to Teams
Events are related records to matches, have a description and a timestamp and a Player with Position (who participated in the event and what position he / she had)
So, I know how to create a Match, assign a Home and Away team to it. Both teams have a list of related players each one.
Then I have a portal where I create events, related with the Match by a key.
I'm having problems assigning a Player Name and Player Position value to the fields in the Events record that should come from the related Players table that is related to the Teams...
I want to use a value list based in a relationship, but I'm really confused.
How can I solve this?