I am creating a db for a school basketball league. I want to track team
stats per game, students stats per game, and games played.
I have the following tables, primary and foreign keys:
students-
pk: studentID fk: teams
studentGameStats (student stats per game)-
pks: studentID/GameID
gameDetails (game date, time, place)-
pk:Game ID
teams-
pk: teamID
teamGameStats(teams stats per game)-
pks: teamID/GameID
I have the game ID which can keep track of the student stats per game in one
table and the team stats in another table but I'm trying to relate the
student stats table and the team stats table so that I can show a team and
the players on the team for any particular game. I'm having a problem
relating these tables because they create a many to many relationship. How
can I resolve this issue and what would be the primary key(s).
Thanks for reading. Any suggestions would be appreciated.
AEA
To be honest, I don't see the need for teamGameStats at all: shouldn't you
be able to derive it by summing the stats for each player on the team?
In any case, many-to-many relationships are resolved by introducing an
intersection entity that has the PKs of the two related tables. Take a look
at the Northwind database that comes with Access. The relationship between
Products and Orders is a many-to-many (a product can exist on many orders,
and an order can contain many products). The intersection entity there is
the Order Details table. For an example of how to handle updates, see the
Orders and Orders Subform forms.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"AEA" <A...@discussions.microsoft.com> wrote in message
news:D67013EB-40D2-4E6E...@microsoft.com...