Table Design (2000)

[waveylines]

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

[MarkLiquorman]

I'm not sure I completely undertstand all the ramifications, but it seems to me that you may have complicated the situation with [tbl Team Fixtures Details]. Why can't you directly link [tbl Teams] with [tbl Fixtures]?

[waveylines]

I think you're right Mark. I've removed the [tbl Team Fixtures Details] junction box and directly linked [tbl Teams] with [tbl Fixtures]. I didn't think I could do this at first, but now I'm pursuing that line of attack !!
I also removed the unique field in the [tbl Team Player Details] junction box, which seems to have solved one of my biggest problems.

Thanks for your help, it's much appreciated and I'm now starting to see some solutions to my problems

[charlotte]

May I make one additional suggestion? Get rid of the spaces in your table names. They aren't needed and they cause problems since you *always* have to use brackets around the name if it contains a space. The standard convention is to make the tag (tbl) lowercase and then to capitalize the first letter of each word in the name. That makes them easy to read, easy to type, and you don't have to mess with brackets all the time.

[waveylines]

Thanks Charlotte. I never realised the space forced the use of brackets, that's an excellent tip.