Conditional Relationships?

[alistair.calder]

I am trying to create a hockey player database in Access, so I have
created a Player table that contains things like Name, Age, etc.

I have another table called Stats which contains GamesPlayed, Goals,
Assists and stuff. But this table of stats only works for forwards and
defensemen.

I am also tracking goalies, that have a totally different set of stats
(GoalsAllowed, ShutOuts, Shots, etc). So I have created a separate
table GoalieStats.

What I want to know is: From the Players table, can I have a link to
the Stats table IF the player is a forward/defenseman, and link to a
different database if the player is a Goalie?

Alistair

 

[John Vinson]

What I want to know is: From the Players table, can I have a link to
the Stats table IF the player is a forward/defenseman, and link to a
different database if the player is a Goalie?

Nitpick: you're not relating *DATABASES*, you're relating *TABLES*. A
Database (in Access jargon) is the container for multiple tables,
forms, reports and other objects.

WHat you're describing is a good example of the moderately advanced
topic of "Subclassing" - one of the few instances where "One to One"
relationships are appropriate. In your case you would have a table of
Players, related one to one - actually this is shorthand for "One to
(zero or one)" - to a table of Stats, and also related similarly to a
table of GoalieStats.

To enforce the rule that only Goalies can link to GoalieStats and only
forwards and defensemen to Stats will require that you do your data
entry through Forms; these would have some VBA code to control which
related table you see. One way to do this would be to change the
Recordsource of a subform on the basis of the value of the player's
Position field.

John W. Vinson[MVP]

 

[alistair.calder]

You are right, I got lazy in my question and put database in there by
mistake.

As for the rest of your answer, I appreciate the response, but it looks
like more than what knowledge I have acquired about Access.

I have (reluctantly) decided to build 4 tables:

1. Players
2. PlayerStats
3. Goalies
4. GoalieStats

All within the same database, as the Players and Goalies tables still
need to relate to the Salaries, Teams and Owners tables.

Bummer, but I don't have the time to learn VBA. Maybe for next year's
draft.

Thanks for the help though... at least I didn't spend days trying to
figure out something I wouldn't have been able to do.

A.

 

[Arvin Meyer [MVP]]

Before John's blood pressure goes up, let me offer another plan. You only
need 2 tables, but can use 3 tables. Goalies are players and GoalieStats are
still stats (although there may be differences) You can add fields to the
PlayerStats table and validate their use by a requirement of have a Goalie
as a position, or you can have the one-to-one table for the GoalieStats. If
I were making the decision, it would be soley based upon whether or not
enough of the fields in the GoalieStats table were identical to those in the
PlayerStats table. In baseball, that would be the case for pitchers, at
least in the National League, where the fielding and hitting stats are the
same.

The VBA code is simple enough, and if you respond with you field and table
names, I'm sure you'll get some help.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[John Vinson]

Bummer, but I don't have the time to learn VBA. Maybe for next year's
draft.

Thanks for the help though... at least I didn't spend days trying to
figure out something I wouldn't have been able to do.

Sorry! I didn't mean to be THAT discouraging.

You can certainly use the Players, Stats, and GoalieStats tables; if
you're careful to only enter goalie stats in the GoalieStats table and
only other player's stats in the Stats table you'll be fine. What
needs code (and only a few lines that we can help you with) is to have
the Form itself enforce this limitation.


John W. Vinson[MVP]

 

[alistair.calder]

Alrighty, here are the tables in question:

PLAYERS
- PlayerID (Primary Key)
- FirstName
- LastName
- Position (Winger, Center, Defense or Goalie)
- TeamID (Links out to TEAMS table)
- OwnerID (Links out to OWNERS table)

PLAYERSTATS
- PlayerID
- GamesPlayed
- Goals
- Assists
- ShotsOnGoal
- PlusMinus
- GameWinGoals
- GameTieGoals
- PowerPlayGoals
- PowerPlayAssists

GOALIESTATS
- PlayerID
- GamesPlayed
- Saves
- GoalsAllowed
- Shutouts

Also, here are the other tables I am using (just FYI):
SALARY (Our pool uses a Salary Cap, so Salary is critical)
- PlayerID
- 2005
- 2006
- 2007
- 2008
- 2009
- 2010

OWNERS (These are the members of my Hockey Pool)
- OwnerID (Primary Key)
- OwnerName

TEAMS (These are the actual NHL teams each player plays for)
- TeamID (Primary Key)
- TeamName

 

[John Vinson]

On 25 Sep 2005 13:57:47 -0700, (e-mail address removed) wrote:

Well... you didn't ask a question, specifically, but I'll make some
comments.

Alrighty, here are the tables in question:

PLAYERS
- PlayerID (Primary Key)
- FirstName
- LastName
- Position (Winger, Center, Defense or Goalie)
- TeamID (Links out to TEAMS table)
- OwnerID (Links out to OWNERS table)

looks good...

PLAYERSTATS
- PlayerID
- GamesPlayed
- Goals
- Assists
- ShotsOnGoal
- PlusMinus
- GameWinGoals
- GameTieGoals
- PowerPlayGoals
- PowerPlayAssists

If you were to record information about each game, you could calculate
all these stats dynamically with a Totals query. As it is, won't you
have to update the table after every game, for every player in that
game?

GOALIESTATS
- PlayerID
- GamesPlayed
- Saves
- GoalsAllowed
- Shutouts
ditto...

Also, here are the other tables I am using (just FYI):
SALARY (Our pool uses a Salary Cap, so Salary is critical)
- PlayerID
- 2005
- 2006
- 2007
- 2008
- 2009
- 2010

STOP. The structure of this table IS INCORRECT.

It should have three fields: PlayerID, YearPlayed (don't use the
reserved word Year as a fieldname), Salary. If a player plays in four
years, you'll have four records. You can regenerate the appearance of
your current table using a Crosstab Query.

OWNERS (These are the members of my Hockey Pool)
- OwnerID (Primary Key)
- OwnerName

TEAMS (These are the actual NHL teams each player plays for)
- TeamID (Primary Key)
- TeamName

ok... that's all fine.

I'd suggest using a Form based on Players, with a Tab Control. Put a
subform based on GoalieStats on one page, and another based on
PlayerStats on another.

The simple solution would be just to trust the user (you?) to enter
data only on the proper tab page. The more elaborate solution would be
to have both Subform's Enabled property set to False; in the
mainform's Current event *and* in the AfterUpdate event of the control
(a combo box, I'd assume) bound to Position, something like:

Private Sub Form_Current()
If IsNull(Me!cboPosition) Then ' Is this a new record?
' Leave both subforms off
Me!subPlayerStats.Enabled = False
Me!xubGoalieStats.Enabled = False
Else
' Set the appropriate subform on
Me!subPlayerStats.Enabled = (Me!cboPosition <> "Goalie")
Me!subGoalieStats.Enabled = (Me!cboPosition = "Goalie")
End If
End Sub

John W. Vinson[MVP]

 

[alistair.calder]

Okay, I appreaciate the help so far!

Yes, I was wondering about the Salary table. It makes total sense that
I can do this with a 3 Column table (PlayerID, YearPlayed, Salary).
Thanks for that.

As for the rest of the stats, I only need stats for past years: 2001,
2002 and 2003 (there was no 2004 season). This is a draft pool,
meaning we pick the players at the beginning of the year and then hang
onto that list until the playoffs. As a result, there is no
requirement for updating records, this database will only be used twice
a year: Once in the fall (before the season starts) and once in the
spring (before the playoffs).

Finally, a point I should have raised right from the beginning: I
apologize for not doing so before: I already possess all of the stats,
salaries and other data on each player, but it is all in a series of
Excel spreadsheets. The reason I am creating this database is so that,
on draft day, I can review the remaining list of players (each time a
player is chosen, I will update the OwnerID from NONE to one of the
owners) by any number of criteria I find important at the time.

As an example, late in the pool, I have $3M remaining under my salary
cap, and I need an effective scorer. So, I might look for someone who
has >40 Points in 2003 and is projected to make <$3M in the next
season. I would then get a list of players I can review and decide
which one to draft. In each round, I will have about 5 minutes to do a
large number of these kind of queries.

Thanks,
Alistair

 

[John Vinson]

Finally, a point I should have raised right from the beginning: I
apologize for not doing so before: I already possess all of the stats,
salaries and other data on each player, but it is all in a series of
Excel spreadsheets.

You can use File... Get External Data... Link to connect to these
spreadsheets, and a series of Append queries to migrate the data into
your normalized tables - might save you a passel of reentry work.

Do the Queries and Forms seem reasonable so far? Any outstanding
problems?

John W. Vinson[MVP]

 

[alistair.calder]

Okay, I have managed to put the database together and it's working
quite well for retrieving data, thanks for the help.

I do have one last question though: I have built a few queries that
pull out available players, players by position, etc.

However, one of the most critical parts to the pool is marking off all
the players that have been taken by other owners during the draft (this
happens quickly).

I need a way to quickly search for a player (I usually do this by last
name) and then select who took it from a drop-down box, having it
update the player record. Is this easy to do?

 

[John Vinson]

I need a way to quickly search for a player (I usually do this by last
name) and then select who took it from a drop-down box, having it
update the player record. Is this easy to do?

Certainly. Use a Form based on the player table. Use the Combo Box
Wizard to put a combo on the form using the option "use this combo to
find a record" - base the combo on a query sorting the player's names
alphabetically so you can quickly find the combo; have another combo
of owners bound to the OwnerID field.

You could then just open the form, select the player, select the new
owner, and you're done.

John W. Vinson[MVP]