Table and replationships

[AndyPandy]

I have already created a database that stores and logs my teams and players scores, however I want to amend it so that I can create a league table for the teams that are in it etc based, I know I might have to start again so can anyone help.

Tables that I have come up with so far are as follows.

DivisonID
Div Type

TeamID
Team Name

PlayerID
Player
Score

DateID
Date of Match

FixtureID
Home/Away (Look Up Field)


Can anyone help with the layout of these tables and there relationships or can anyone help by re-jigging the database that I have already created which if anyone wants to see it i will upload later today

 

[CJ_London]

This might suit your needs more closely

tblDivisions
DivisonID autonumber PK
DivName text

tblTeams
TeamID autonumber PK
TeamName text
DivisionID long FK

tblPlayers
PlayerID autonumber PK
PlayerName text
TeamID Long FK

TblFixtures
FixtureID autonumber PK
FixtureDesc text e.g. final, quarter final, etc
DivisionID long FK - note not neccesarily required - can be found by going through either HomeTeam or AwayTeam
HomeTeamID long FK
AwayTeamID long FK
FixtureDate Date

TblScores
ScoreID autonumber PK
FixtureID long FK
PlayerID long FK
Score integer

Each PK has a one to many relationship with each FK of the same (or similar (home/away)) name

 

[AndyPandy]

Thanks

This layout looks about wright but tried to create a form and it says record not in record set so how is it the relationship can anyone shed any light on this.

 

[CJ_London]

Would need to see what you have rather than what I suggested in o5der to help you on this

 

[AndyPandy]

I have the folowing

tblDivisions
DivisonID autonumber PK
DivName text

tblTeams
TeamID autonumber PK
TeamName text
DivisionID long FK - Linked to Div ID in Division Table

tblPlayers
PlayerID autonumber PK
PlayerName text
TeamID Long FK - Linked to teamID in Teams Table

TblFixtures
FixtureID autonumber PK
FixtureDesc text e.g. final, quarter final, etc
DivisionID long FK - note not neccesarily required - can be found by going through either HomeTeam or AwayTeam
HomeTeamID long FK
AwayTeamID long FK
FixtureDate Date

TblScores
ScoreID autonumber PK
FixtureID long FK - Fixture ID Linked to Fixture ID in Fixture Table
PlayerID long FK - Linked to Player ID in player table
Score integer

The sport is called crown green bowls and you have home team players and the visiting team players

Could it be because i am using form wizard instead of creating the form manualy that it is not working.

Also where we have got home team id and awayteam id i dulicated the teams table in the relation ship view so it read teamstable linked to home teamid and teamstable1 - linked to awayteam id

 

[CJ_London]

you need to define 'not working'!

Wizards are OK but generally don't do enough.

I'm away for a couple of days so if anyone else can help it will be appreciated

 

[AndyPandy]

When I tied to use form wizard to create a form

I selected Div. Teamname the form was created but it would not let me enter any info into the team field it came up saying not in record set. This form was to be used to allow me to add teams to a division etc.

Shall I not use form wizard and do it manually.

 

[Helystra]

Andy...it looks to me like you were selecting tblDivisions to create the form and then trying to alter a record that is part of tblTeams. You need to select the right table when using the wizard so that you can alter the records within that table.