Trying to create a solution for tracking athletic games and tracking the statistics generated by them. Keeping it simple, I want to track games where each game can have two teams and there can be as many as 50 games played in a week and over 400 in a season. I'd like to track each game in a Game table with each record containing the names of each team and other information like date, time, scores, etc. After the weekly games are played I want to separate each team from the game record and the scoring information and have it in a Team table where I can generate sub-summaries that would give the averages for scores, etc. When the next week of play occurred I would like to take these averages in the Team table and have them available in the Game Table where new games are played by different teams (No two teams play each other twice unless its in the playoffs after the regular season is over).
In summary, I have multiple games each week. Each game (record) has two teams (visitor and home) and the scores after they have played (visitor score and home score). After all games have been played each team in the game would become a single record in another table where the individual teams statistical profile (averages of the visitor score, home score. etc.) could be computed. Each teams statistics would then be available for the following week of play but each game would have different teams playing different opponents (no two teams would play each other twice unless it would be in the playoffs after the regular season). When a new record (game) is created and each team is put into the record, their averages would come in from the Team table.
I'm rather new to creating relationship and using primary key fields, so I'm struggling on how to create the relationship for the games (Game ID) that contains two teams (no key ID), with the teams (Team ID). Also I'm not sure that even if a relationship could be created that works, whether the sub-summaries in the Team table could be displayed in the Game table. I don't think it's a Lookup as the averages will change from week-to-week. I tried to just import them from the Team table but couldn't do that, which may also have something to do with the relationships.
Have been trying to solve this for some time so would appreciate any guidance.