Can anyone help with problems designing the table design for a Tennis League. A Team can has numerous Players, and a Player can actually play for more than one team. A Team has various Fixtures (a Match) and each match is played between two teams. Therefore I have set up two junction tables. However, each Match played consists of 4 games (Home Team Player 1 v Away Team Player 1, Home Team Player 2 v Away Team Player 2 etc). I will need a form to set up the match, by choosing the two teams, selecting the Players, and assigning scores to those players, which will then determine the overall match result. I am struggling to set up the "tbl Games" and seeing how this could work. Am I on the right lines with the table Design? How can I link two fields (two Players in a game) back to the tbl Players to ensure integrity?
Thanks for all your help