Access 2010 correctly configure tables and relationships to retrieve all records
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.
I had considered separating the game table into two tables but I am still faced with the same problem.
In the current design, if I want to get all of the games played by Team A, I need to interrogate all records in the GamesID table but the problem is looking for two fields in one record. If I split the table into two I need to check all records in two tables. I'm not sure how I can get this to cascade.
I tested splitting the game table into a home and visitor table but in datasheet view, when I click on the + sign to expand the next level after TeamSummary, I am presented with a dialog to select which table should be next in the path so I can select either home or visitor.
This would appear to be an inherent design problem as there is a split path between the team table and the next step to the game data, either home/visitor field in a record or home/visitor table. Perplexing.
Thanks tqfdotus, hope I'm not missing something simple.