Sports DB

W

Whodat

Hi,
With some guidance from this and other groups I have developed a working DB
to track the statistics of a mens hockey team.
My hope is to keep an ongoing track of season and career stats.
Currently the DB is layed out as follows:

Table : Team_Stats
Gameday
Opponent
Goalsfor
Goalsagainst
Win
Loss
Tie
Shutout
GameID (autonumber)

Table Players:
PlayerID (autonumber)
Playername
Jerseynumber

Table Playerstats:
StatID (autonumber)
StatPlayerID (from players)
StatGameID (from Team_stats)
StatGoals
StatAssists
StatPoints
StatPIMS

When I created this DB I failed to include a way to track games played. Does
anyone have any suggestions for a good way to work it in? Also will I be
able to query information from specific dates so that I can track totals for
a given season? Thanks for reading

Regards
Scott
 
J

Jackie L

What further information do you need for your games. It looks as though you
have the start of it in the Team_Stats table. I would not recommend another
table since it would create a one to one relationship which there is no need
for. As long as the player stats are linked to the Team_Stats by the
StatGameID field, you should be able to create queries or reports with the
date range selection being on the Team_Stats gameday field. I have no idea
as to the structure of the program but as you may already know, it is best to
put the date range fields as unbound on a form and refer to them on the
criteria line of your query.

Another hint that may help with future projects is to use values that exist
in your table as key fields when possible and avoid the autonumber fields.
This will allow you to restrict your data to avoid entry errors, amoung other
reasons. For instance, your Team_Stats table could have GameDay and
GameNumber or GameDay and OpponentID as the key fields. PlayerID could be a
few digits from the last name and then a numerical code, ie. SMI001 for
Smith. This also allows for ease of data entry. Then, looking at your
Player stat table, you could identify a record for the player and game
without even linking to the Team_Stat if needed.

Just a thought. Hope it helps.
 
M

Mike Painter

Whodat said:
Hi,
With some guidance from this and other groups I have developed a working
DB to track the statistics of a mens hockey team.
My hope is to keep an ongoing track of season and career stats.
Currently the DB is layed out as follows:

Table : Team_Stats
Gameday
Opponent
Goalsfor
Goalsagainst
Win
Loss
Tie
Shutout
GameID (autonumber)

Table Players:
PlayerID (autonumber)
Playername
Jerseynumber

Table Playerstats:
StatID (autonumber)
StatPlayerID (from players)
StatGameID (from Team_stats)
StatGoals
StatAssists
StatPoints
StatPIMS

When I created this DB I failed to include a way to track games played.
Does anyone have any suggestions for a good way to work it in? Also will I
be able to query information from specific dates so that I can track
totals for a given season? Thanks for reading
Click to expand...

If gameday is the date of a game you will be able to track by dates.

With a separate field for win, loss, tie shutout, out will have a hard time
showing totals for each.
You actually don't need any of these fields since they can (I assume) be
calculated from the game score using a case statement.
 
Top