Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    table relationship ? (Access 2002)

    I'm stuck on something that is probably quite simple. I am trying to create a database to track basketball games, scores, players, and player
    statistics. I have related tables for: teams, players, games, playerstatistics, and scores. I am stuck on the best way to relate teams to
    the games (two teams participate in every game). I have read about junction tables being used to create a many to many type relationship behavior. Does
    this sound like the right approach? any thoughts would be appreciated. thank you. scott

  2. #2
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table relationship ? (Access 2002)

    Hi Scott, yes junction tables would do it, something like tblJunctionGameTeam, with primary keys from the participating tables, e.g. GameId and TeamId.

    Regards.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: table relationship ? (Access 2002)

    In this case, there are always two teams in a game: the home team and the visiting team. So you could put fields for HomeTeam and VisitingTeam in the Games table, and join the Teams table to both (when you add the second relation, Access will ask you whether you want to edit the existing (first) relation; answer No.

    An alternative would be to create a many-to-many relation by creating a junction table. This table would contain GameID, TeamID and probably an indication of whether the team represented by TeamID is the home team or visiting team for this game. However, you would have to make sure that there would always be exactly two records for each GameID in this table.

  4. #4
    Star Lounger
    Join Date
    Oct 2001
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table relationship ? (Access 2002)

    Thank you. I'm going to try both ways.
    thanks again.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: table relationship ? (Access 2002)

    I know this seems like it should be a simple problem, but the truth is it isn't. One complication is that player's move from team to team, so you may want to keep that in mind if that's an issue. You may also want to track At Bats for players as well if you want flexible statistics, and the same is true of fielding attempts and balls and strikes. But it sounds like a fun project to work on! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Wendell

  6. #6
    Star Lounger
    Join Date
    Oct 2001
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table relationship ? (Access 2002)

    Thanks for your message. I am currently playing with tracking that very thing so statistics will be available for a player by teamID, gameID, etc. so the stats will always be intact regardless if a player is traded. It is definitely an onion! thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •