A
alistair.calder
I am fairly new to Access and Database design, but I think I can learn
what I need to fairly quickly (other than hard-core coding).
I don't necessarily need someone to formulate a complex answer or do
any programming for me: that's not what I am after. I am looking for
someone to review my requirements (below) and then answer the questions
I have posed:
** MY QUESTION TO THE GROUP:
- For what I have done so far, am I a long way off from what I want to
accomplish?
- Is this something I am going to be able to complete with, say, 10 -
15 hours worth of work?
- Will I have to do a lot of programming?
- Is there an example somewhere that I can model this on?
** Background:
I have been working on creating a database for an upcoming Hockey Pool
I am in.
Each year I participate in a "lifetime" hockey pool that has 10 people
drafting players from a pre-determined list with a restriction of a
$58M salary cap. The draft goes in rounds, allowing each person to
pick 1 player before the next round begins. Once a player is selected,
that player is no longer available to any of the other people in the
pool. Each year we reduce our rosters to $38M and re-draft from the
rookie pool and the recently released list.
Selecting a player is a matter of reviewing a large set of stats
(usually in Hockey Pool magazines, downloaded stats, excel spreadsheets
and salary lists). The problem is that if you were interested in (for
instance): all players that made less than $2M but had more than 50
points last season, you have to do it manually. It can take forever,
especially considering the Salary and Points lists are on different
sheets.
** My Idea:
I have imported all of the data sets into a number of tables in Access
(see Table List below), so that I can quickly run queries (on a number
of different criteria), look up specific players (so I can mark them as
'drafted' once someone picks him), and other functions.
The players in the PLAYERS table include all players in the NHL,
whether they are owned in the pool or not. I have a OWNER table that
contains the names of the people involved in the pool.
** Problem:
So, I have created the tables, created the keys and even took a pass at
creating relationships. But what I really need to do now is be able to
view this information all in one place. Here is how I imagine
scenarios (I need to be able to do all of these quite quickly):
1. Reviewing Available Players: I would like to view a list of all
players that have no owner, and then be able to sort that list by
Goals, or Salary or Position.
2. Salary vs. Points: I'd like to view all players of a certain
position that make less than $X and scored more than Y points.
3. Who has Who: Select all players that are owned by Owner X
And a few more like that.
** User Interface:
I would like to have a simple interface that contains:
PLAYER NAME - TEAM - POSITION - OWNER
SALARY LIST 2005 - 2010
STATS 2003
STATS 2002
STATS 2001
** Table List (with notes):
PLAYERS (This is the Hockey Players)
- PlayerID (Primary Key)
- FirstName
- LastName
- Position (Like Center, Defense, Winger)
- TeamID (Linked to TEAM-TeamID)
- OwnerID (Linked to OWNER-OwnerID)
STATS
- PlayerID (Primary Key)
- Year
- GamesPlayed
- Goals
- Assists
(There are a number of other items in this list, but I've kept it
short)
SALARY
- PlayerID (Primary Key)
- 2005
- 2006
....
- 2010
TEAM (The team that the NHL Player plays for)
- TeamID (Primary Key)
- TeamName
- TeamLogo
OWNER (These are the members of the lifetime pool, plus an Owner named:
None)
- OwnerID (Primary Key)
- OwnerName
- OwnerTeamName
what I need to fairly quickly (other than hard-core coding).
I don't necessarily need someone to formulate a complex answer or do
any programming for me: that's not what I am after. I am looking for
someone to review my requirements (below) and then answer the questions
I have posed:
** MY QUESTION TO THE GROUP:
- For what I have done so far, am I a long way off from what I want to
accomplish?
- Is this something I am going to be able to complete with, say, 10 -
15 hours worth of work?
- Will I have to do a lot of programming?
- Is there an example somewhere that I can model this on?
** Background:
I have been working on creating a database for an upcoming Hockey Pool
I am in.
Each year I participate in a "lifetime" hockey pool that has 10 people
drafting players from a pre-determined list with a restriction of a
$58M salary cap. The draft goes in rounds, allowing each person to
pick 1 player before the next round begins. Once a player is selected,
that player is no longer available to any of the other people in the
pool. Each year we reduce our rosters to $38M and re-draft from the
rookie pool and the recently released list.
Selecting a player is a matter of reviewing a large set of stats
(usually in Hockey Pool magazines, downloaded stats, excel spreadsheets
and salary lists). The problem is that if you were interested in (for
instance): all players that made less than $2M but had more than 50
points last season, you have to do it manually. It can take forever,
especially considering the Salary and Points lists are on different
sheets.
** My Idea:
I have imported all of the data sets into a number of tables in Access
(see Table List below), so that I can quickly run queries (on a number
of different criteria), look up specific players (so I can mark them as
'drafted' once someone picks him), and other functions.
The players in the PLAYERS table include all players in the NHL,
whether they are owned in the pool or not. I have a OWNER table that
contains the names of the people involved in the pool.
** Problem:
So, I have created the tables, created the keys and even took a pass at
creating relationships. But what I really need to do now is be able to
view this information all in one place. Here is how I imagine
scenarios (I need to be able to do all of these quite quickly):
1. Reviewing Available Players: I would like to view a list of all
players that have no owner, and then be able to sort that list by
Goals, or Salary or Position.
2. Salary vs. Points: I'd like to view all players of a certain
position that make less than $X and scored more than Y points.
3. Who has Who: Select all players that are owned by Owner X
And a few more like that.
** User Interface:
I would like to have a simple interface that contains:
PLAYER NAME - TEAM - POSITION - OWNER
SALARY LIST 2005 - 2010
STATS 2003
STATS 2002
STATS 2001
** Table List (with notes):
PLAYERS (This is the Hockey Players)
- PlayerID (Primary Key)
- FirstName
- LastName
- Position (Like Center, Defense, Winger)
- TeamID (Linked to TEAM-TeamID)
- OwnerID (Linked to OWNER-OwnerID)
STATS
- PlayerID (Primary Key)
- Year
- GamesPlayed
- Goals
- Assists
(There are a number of other items in this list, but I've kept it
short)
SALARY
- PlayerID (Primary Key)
- 2005
- 2006
....
- 2010
TEAM (The team that the NHL Player plays for)
- TeamID (Primary Key)
- TeamName
- TeamLogo
OWNER (These are the members of the lifetime pool, plus an Owner named:
None)
- OwnerID (Primary Key)
- OwnerName
- OwnerTeamName