A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Player Statistics



 
 
Thread Tools Display Modes
  #1  
Old December 29th, 2009, 12:24 AM posted to microsoft.public.access.tablesdbdesign
peterkelly
external usenet poster
 
Posts: 3
Default Player Statistics

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

  #2  
Old December 29th, 2009, 12:45 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Player Statistics

On Mon, 28 Dec 2009 23:24:54 GMT, "peterkelly" u57116@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]
  #3  
Old December 29th, 2009, 02:10 AM posted to microsoft.public.access.tablesdbdesign
peterkelly
external usenet poster
 
Posts: 3
Default Player Statistics

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

  #4  
Old December 29th, 2009, 02:55 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Player Statistics

On Tue, 29 Dec 2009 01:10:46 GMT, "peterkelly" u57116@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]
  #5  
Old December 29th, 2009, 04:21 AM posted to microsoft.public.access.tablesdbdesign
peterkelly
external usenet poster
 
Posts: 3
Default Player Statistics

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.