I have a functioning Access 2010 DB that I use to keep track of basketball teams for scouting purposes. The basic table concept/design is:
Year>School>Team>Team Summary>Game>Quarter Scores>Player Points>Players
So in any given year a school has a team (players and coaches) that play games and report scores for both the team and the players. Here's the problem.
When setting the relationships, The game table has two entries, one for the Home team, one for Visitor. I can only link from Team Summary to either Home or Visitor in the GameID table but have not been able to work out a design that would allow me to get both home and visitor records as part of the 'normal flow'.
I have been using queries to get to this data but wonder if I have overlooked a re-design that would return both home and visitor records as the result of a relational design rather than making use of queries. I have attached a pdf with the relational map.