Prev: Set auto#
Next: Table structure for Course Attributes
From: peterkelly on 28 Dec 2009 18:24
Hi,
I have an access members database, quite extensive. I also have a members
excel file which details frames won and lost in each match for the season (8-
Ball Pool). This will then calculate a members "statistics".
I would like to incorporate this all into access but I cannot get my brain
into how.
How do I record each players results each week, for each team without having
a massive table. ie Round1Win
Round1Loss
Round2Win
Round2Loss
etc
I also have another escel file for the competition ladder. I would also like
to have this in access.
At the moment there are 5 Divisions of 10 teams witha minimum of 8 players in
each team.

Any help wpuld be appreciated.
Thanks

Peter

From: John W. Vinson on 28 Dec 2009 18:45
On Mon, 28 Dec 2009 23:24:54 GMT, "peterkelly" <u57116(a)uwe> wrote:

>Hi,
>I have an access members database, quite extensive. I also have a members
>excel file which details frames won and lost in each match for the season (8-
>Ball Pool). This will then calculate a members "statistics".
>I would like to incorporate this all into access but I cannot get my brain
>into how.
>How do I record each players results each week, for each team without having
>a massive table. ie Round1Win
>Round1Loss
>Round2Win
>Round2Loss
>etc
>I also have another escel file for the competition ladder. I would also like
>to have this in access.
>At the moment there are 5 Divisions of 10 teams witha minimum of 8 players in
>each team.
>
>Any help wpuld be appreciated.
>Thanks
>
>Peter

Access tables (like all relational tables) are tall and thin, not wide and
flat. "Fields are expensive, records are cheap" is an old saying that's very
good advice!

You would need several tables:

Members
MemberID
LastName
FirstName
<other contact information>

Teams
TeamID
TeamName
<other info about the team as an entity in its own right>

TeamMembers
TeamID <link to Teams>
MemberID <link to Members>

Divisions
<well, I hope you get the idea>


The win/loss data would be stored, again, in a tall-thin table. I don't know
the sport well enough to know - are rounds played between individuals, or
teams? Either way you would store one RECORD (not two fields) per round, e.g.

GameDate
Player1ID
Player2ID
Player1Score
Player2Score

and you wouldn't store the won or lost at all, just calculate it on the fly
based on the scores.
--

John W. Vinson [MVP]
From: peterkelly on 28 Dec 2009 20:10
Thanks John

The games are between one player from each team at a time. Each Player will
play 4 players from the opposition. So therefore there are 32 games in a
match.

If I get the idea....

tblDivisions
DivisionID
TeamID <link to Teams>
MemberID <link to Members>

tblMatchDate
DivisionID <link to tblDivisions
TeamID <link to tblTeams>
Player1ID
Player2ID etc to Player 8
Player1Score
Player2Score etc to Player8

If this is what I need, how do I then calculate players statistics and a
ladder?

Thanks for the help

Peter

From: John W. Vinson on 28 Dec 2009 20:55
On Tue, 29 Dec 2009 01:10:46 GMT, "peterkelly" <u57116(a)uwe> wrote:

>Thanks John
>
>The games are between one player from each team at a time. Each Player will
>play 4 players from the opposition. So therefore there are 32 games in a
>match.
>
>If I get the idea....
>
>tblDivisions
>DivisionID
>TeamID <link to Teams>
>MemberID <link to Members>
>
>tblMatchDate
>DivisionID <link to tblDivisions
>TeamID <link to tblTeams>
>Player1ID
>Player2ID etc to Player 8
>Player1Score
>Player2Score etc to Player8

Sorry, but you're still not getting it.

Again... "fields are expensive, records are cheap".

If there are 8 games (or 120 games) on a matchdate, you need 8 *RECORDS* (or
120 records) in a table related one to many to the primary key of
tblMatchDate.

>If this is what I need, how do I then calculate players statistics and a
>ladder?

Since I have no idea how the statistics or a "ladder" are calculated in the
real world, outside of the database, all I can say is "with an appropriate
query".

--

John W. Vinson [MVP]
From: peterkelly on 28 Dec 2009 22:21
John

Would it be like this

tblMatchDate
MatchDateID
DivisonID <link to tblDivisions>
TeamID <link to tblTeams>
Date

tblMatchRecord
MatchDateID <link to tblMatchDate>
MemberID <link to tblmembers>
Result "calculated result"

Peter

 | 
Pages: 1
Prev: Set auto#
Next: Table structure for Course Attributes