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.

Regards,


Charley

tblParticipants
BadgeNumber (PK Autonumber?)
FirstName
LastName
TeamID (FK)

TblTeams
TeamID (PK Autonumber)
Team

tblGames
GameID (PK autonumber)
Game
Schedule

tblWinners
WinnerID (PK Autonumber)
GameID (FK)
BadgeNumber (FK)
PlaceID (First, Second, Third)

tluPlacePoints
PlaceID (PK)
Place

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.

ScottGem,

Thanks. Excellent. Let me work around with this.

Regards,

Charley

To get maximum flexibility and to use the same database in the future, I'd add a table:

tblGamePlace
GamePlaceID Autonumber PK
GameID FK to tblGames
PlaceID FK to tblPlaces
PlacePoints Number

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.

Thanks. Really an ammendment to Scott's solution.

Regards,

Charley