Mar 18 2007, 05:25 AM
I need to develop a database for sports day.
I created a ParticipantTable which has the following fields
Badge Number (Unique ID of the participant)
Name (Participant Name)
Team (Team to which the participant belongs)
I have a Table called Game which lists all the game names
A game can have 3 winners. Winner1 gets 3 points, Winner2 gets 2 points and Winner3 gets 1 point
What i would like to do is record against each game the winners and finally get the total Score per team(through a crosstab query ?). Am getting little lost on how to create a table for recording the game and winners. Should i need to create 3 different fields for 3 winners? Can someone please advise me on the table relationship. Am attaching the sample db.
Mar 18 2007, 08:18 AM
BadgeNumber (PK Autonumber?)
TeamID (PK Autonumber)
GameID (PK autonumber)
WinnerID (PK Autonumber)
PlaceID (First, Second, Third)
That should give you what you need to report points per team. I would manually set the PlaceID at 3 for First etc.to make totally the points easier.
Mar 18 2007, 08:44 AM
Thanks. Excellent. Let me work around with this.
Mar 18 2007, 08:50 AM
To get maximum flexibility and to use the same database in the future, I'd add a table:
GamePlaceID Autonumber PK
GameID FK to tblGames
PlaceID FK to tblPlaces
Not saying Scott's suggestion of getting double duty from a field is bad, but, if you want to use it again, I wouldn't do it that way. This way, if you have another game with say, 10 places, and places 5-7 get the same points, you could still handle it. Using PlaceID for the points as well wouldn't allow it.
Mar 18 2007, 11:29 PM
Thanks. Really an ammendment to Scott's solution.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here