How to set up table to Track Tennis matches for College Team 2

[netrusher]

I need to create a table to store tennis match information. I then need to create a form to enter the data. I can do all of this but I am having some issues with my thought process.
There will be multiple matches (10?) during the season. For each college match there will be 6 singles matches and 3 doubles matches. The six singles matches will be seeded 1,2,3,4,5,6 and the 3 doubles matches will be seeded, 1,2,3. I will need to enter my players name and opponent for each of the six singles matches and two of my players names and two opponents name for the three doubles matches. I will also need to insert a score for each match. The score will be entered at a later date after the match is completed. I have some basic knowledge of databases. My dilemma is as follows: How do I set up the table to enter names for each Singles entry and each Doubles entry. Go back later and enter the score to the same record. This would allow me to later query the table for records for individuals and scores for individuals. I do not see how to accomplish this without having a separate name field and score field for each Singles and Doubles entry. I am really open for advice and thanks.

 

[MajP]

You will need several tables and need to understand how to make a many to many relationship in access using a junction table. Here is one reference on the many to many.

http://www.techrepublic.com/article/accommodating-a-many-to-many-relationship-in-access/5285168

But this is a challenging database design because of the multiple relationships. Each table is very succinct but you need several tables with both one to many and many to many relations. Read up on database normalization. Lots of references on the internet on relational database design.

Something like
tblPlayers
playerID
playerLastName
other player information

TblTournment
tournamentID
tournamentName
tournamentDate
other tournament info

TblMatch
matchID
matchType (single, double)
tournamentID (relates to the tournament)
other match specific information

TblMatch_PlayerScores
matchID (relates back to tblMatch)
playerID (each player in a match gets a record)
score (score)
winlose (because you may just save scores as text)

Not sure how you plan to enter scores because if you tracking sets then you need another table. But if you just need the string. then:

12 3 "6,3,6,7,6" win
12 7 "4,6,7,6,4" lose

So in tournament 12, player 3 played player 7. Final score was 6-4, 3-6, 6-7, 7-6, 6-4.

If you are tracking the sets then need a set table

tblSets
matchID
playerID
gamesWon
winLoseset

But yes if you want to be able to get specific player scores then you need a record for each. A much more complicted way would be to only save the scores and relate them to the match. Then you would have to write code to parse them out.

 

[netrusher]

Thanks MajP, i will give it a go. Not sure I understand how to get Like PlayerId in the different tables.

 

[MajP]

I would spend some time trying to find a similar template on the net. This should be the kind of thing that has been done many times. MS has some templates you can look at. That way you can get an idea of the structure and the user inputs and features. You might not find a tennis specific but maybe horse show, pinewood derby, chess, basketball, track meet, ...