Design/Development Advice

[DaddyRich]

I'm trying to develop a database that keeps track of player and match details
for my football team. I've reached a bit of a brick wall in that I want to
be able to enter a match (ie 18/02/08 vs ABC United) and store the match
result info in one table. I'd also like to be able to store player stats in
conjunction with the match (ie DaddyRich = 2 goals, 3 assists, 1 player vote
and Ref's man of the match). The thing is I can't work out the best way to
do it !!! Currently I have all the information for the last 2 years (95
matches !!) stored in one giant spreadsheet but it's getting really messy !!!!

Thanks in advance !!!

 

[Olduke]

You want two tables, one for teams one for players.
Don't forget to add a field in both tables for Season (example 2007/08) so
that you can select which season's results you want to see.

How much detail you wish to go into in either table is up to you.
In my database for Teams I have: Season, Won and Lost. The number of
matches played in each season I use the Count function in a query.
For Players I have Season, Played (yes/no) and GoalsScored.

 

[DaddyRich]

Thanks for your really quick response . . . although I don't think I
explained my issue very well !!!

Basically each game I store teh following info relating to the match:

Season, Date, Opposition, League Pos, W/L/D, PGFC Score, Opp Score.

On the same row in my spreadsheet I also store the following info per player
(each player has a number of columns assigned to them in the spreadsheet):

Goals, Assists, Votes, Refs MOM, Rating, Start as Sub

This allows me to generate various tables and reports from the spreadsheet
data. I'd like to be able to replicate the process of inputting the result
and stats in each match but I'm stuck with, initially, a couple of things:

1. How do I create something that allows me to input the match details,
appending information such as goals for/against etc to previous records for
that team to show an all-time statistic.
2. At the same time, whats the best way to create something that allows me
to input the players that have played in the game and store their individual
stats so that I can see things such as their performance over a given season,
in a given game or sonce they started playig for the team.

I think the area that I'm confused about is in creating/appending to tables
from another table/form ???

Many Thanks (and I hope that this is clearer !!!),

Rich.

 

[Amy Blankenship]

Thanks for your really quick response . . . although I don't think I
explained my issue very well !!!

Basically each game I store teh following info relating to the match:

Season, Date, Opposition, League Pos, W/L/D, PGFC Score, Opp Score.

On the same row in my spreadsheet I also store the following info per
player
(each player has a number of columns assigned to them in the spreadsheet):

Goals, Assists, Votes, Refs MOM, Rating, Start as Sub

This allows me to generate various tables and reports from the spreadsheet
data. I'd like to be able to replicate the process of inputting the
result
and stats in each match but I'm stuck with, initially, a couple of things:

1. How do I create something that allows me to input the match details,
appending information such as goals for/against etc to previous records
for
that team to show an all-time statistic.
2. At the same time, whats the best way to create something that allows
me
to input the players that have played in the game and store their
individual
stats so that I can see things such as their performance over a given
season,
in a given game or sonce they started playig for the team.

I think the area that I'm confused about is in creating/appending to
tables
from another table/form ???

Many Thanks (and I hope that this is clearer !!!),

It sounds like you're tracking from the "viewpoint" of one team (i.e., you
say who the opposition is, but the other team playing appears to be
"assumed."). Is that a correct statement of what is going on here? If not,
what is the actual situation?

Will your team members be able to move between teams? Will that be able to
happen during a season?

Are you looking for a good, solid information design that once you're
finished it should pretty much maintain itself, or are you looking for
something that you can get up and running quickly, without getting to deep
into how Access forms and queries work (but will probably need to be
modified later)?

-Amy

 

[Pat Hartman]

Here's my take at an initial structure.

tblPlayers:
PlayerID (autonumber primary key)
FirstName
LastName
other info about this player.

tblTeams:
TeamID (autonumber primary key)
TeamName
other info about this team.

tblTeamRoster:
TeamRosterID (autonumber primary key)
SeasonYear (unique index fld1)
TeamID (fk to tblTeams, unique index fld2)
PlayerID (fk to tblPlayers, unique index fld3)
additional information if necessary

tblGames:
GameID (autonumber primary key)
HomeTeamID (fk to tblTeams, unique index fld1)
AwayTeamID (fk to tblTeams, unique index fld2)
GameDate (unique index fld3)
GameNum (unique index fld4) - this is necessary only if it is possible to
play more than one game against the same team on the same day as it is with
American baseball. We call it a double-header.
GameVenue
HomeTeamScore
AwayTeamScore

tblPlayerGameStats:
GameID (fk to tblGames, unique index fld1)
TeamRosterID (fk to tblTeamRoster, unique index fld2)
game stats for each player

 

[DaddyRich]

The assumption is correct !!!

Basically I want to be able to track the stats of my players over any given
period and all-time. The players wouldn't move between teams, they are set
based on he nights they play. Also, I would like it to be a relatively
self-maintaing database, although undertanding the more advanced
functionality would be good as it means that I can then add other area's to
the system as I go !!!

Thanks

 

[Amy Blankenship]

Here's my take at an initial structure.

tblPlayers:
PlayerID (autonumber primary key)
FirstName
LastName
other info about this player.

tblTeams:
TeamID (autonumber primary key)
TeamName
other info about this team.

tblTeamRoster:
TeamRosterID (autonumber primary key)
SeasonYear (unique index fld1)
TeamID (fk to tblTeams, unique index fld2)
PlayerID (fk to tblPlayers, unique index fld3)
additional information if necessary

tblGames:
GameID (autonumber primary key)
HomeTeamID (fk to tblTeams, unique index fld1)
AwayTeamID (fk to tblTeams, unique index fld2)
GameDate (unique index fld3)
GameNum (unique index fld4) - this is necessary only if it is possible to
play more than one game against the same team on the same day as it is
with American baseball. We call it a double-header.
GameVenue
HomeTeamScore
AwayTeamScore

tblPlayerGameStats:
GameID (fk to tblGames, unique index fld1)
TeamRosterID (fk to tblTeamRoster, unique index fld2)
game stats for each player

The only thing I'd add to that is that you could choose to have a separate
table that identified what stats are possible, then have each stat be a
separate record. This allows you to add more things to keep statistics on
without changing your table structure. However, this makes designing the
forms far more difficult and would require at least some programming to make
it work.

That would look something like this:

tblStatsTrack -stats that you can track
StatID
StatDesc

tblPlayerGameStats
GameID
TeamRosterID
StatID
StatValue

HTH;

Amy

 

[DaddyRich]

Hiya,

How would that work then ??? Oh, and this may be a blonde question, but
what does fk mean - as in " TeamID (fk to tblTeams, unique index fld2) " ????

I had a thought that it might help if I explained some of the results I'd
like to be able to pull out of the database ??? A couple of examples are
below.

Top Goalscorer for Summer 2006 Season.
All-Time Most Assists.
All-Time head to head vs AnyTeam FC
Highest Goalscoring Game.
Most Goals Conceded vs

etc etc !!!!!

Am I trying to achieve the impossible ??? ;P

Thanks again for all your help !!!

 

[Amy Blankenship]

Hiya,

How would that work then ??? Oh, and this may be a blonde question, but
what does fk mean - as in " TeamID (fk to tblTeams, unique index fld2) "
????

FK means "foreign key." A foreign key is a reference to a value in another
table, usually the primary key, that will uniquely identify it in the table
that has the FK in it. For example, if you have a table like this:

Players
PlayerID FirstName LastName
1 John Smith
2 Frank Jones

And then you have a table

Teams
GameID GameName
1 Manchester United
2 Liverpool

Then if you had a table
TeamPlayers
TeamID PlayerID
1 1
1 2

Then you can see that both John Smith and Frank Jones play for Manchester
United, because the TeamPlayers table has a foreign key that points to
Manchester United (1) for both records and a Foreign Key that in record one
points to John Smith and in record two points to Frank Jones.

I had a thought that it might help if I explained some of the results I'd
like to be able to pull out of the database ??? A couple of examples are
below.

Top Goalscorer for Summer 2006 Season.
All-Time Most Assists.
All-Time head to head vs AnyTeam FC
Highest Goalscoring Game.
Most Goals Conceded vs

You would have to calculate these based on your underlying stats.

Am I trying to achieve the impossible ??? ;P

You're trying to do something that isn't easy even for someone who does this
for a living.

HTH;

Amy

 

[DaddyRich]

Thanks for all your help !!!

I'm sure I'll find something else that sounds simple and turns out to be a
right pain in the posterior shortly for you lol