Duplicate Fields Problem in My Tables

[Ennead]

I'm working on a DB for statistics for a small Hockey League. We've been
using Excel, but it's getting more and more unwieldy. I've read elsewhere in
this forum that Excel users usually have trouble switching to Access, and I'm
here to tell you it is true! Oh, well, here's what I have so far.

1. A Members table for storing names, addresses, etc.
2. A Roster table for members who are actively playing in the current
season, including team name, jersey number, etc.
3. A Stats table for saving the stats for each player for every game they
play, goals, assists, team, game, etc.

The problem is that all the data in the Roster table has to be duplicated in
the Stats table because Stats remembers everything uniquely and permanently,
while Roster changes from season to season. I understand that having
duplicate fields in different tables is poor design, and this approach has
certainly bogged down for me. I've searched the forum and googled this
issue, but I'm either asking the wrong question or not recognizing the answer
when I read it. Any help will be greatly appreciated. Thanks!

 

[Steve]

Try these tables:

TblYear
HockeyYear

TblTeam
TeamID
TeamName

TblMember
MemberID
<name, address, etc>

TblRoster Each record is a player. For a given year,
RosterID identifies his team, jersey number, etc.
HockeyYear
TeamID
MemberID
JerseyNumber
<other Roster fields>

TblSchedule Schedule of games. Identifies date of game
ScheduleID and which teams are playing in the game
ScheduleDate
HomeTeamID Relate to TeamID in TblTeam
VisitingTeamID " " "

TblStat
StatID
Stat goals, assists, etc (not team or game)

TblPlayerStat
PlayerStatID
ScheduleID
RosterID
StatID
PlayerStat

Steve
(e-mail address removed)

 

[Ennead]

If you

post what you have so far it may be possible to help steer you in the right
direction.

I hope this is what you were requesting. If you were thinking of the actual
mdb, I could post that, too, if you tell me how. Here are the main tables
with the fields they contain:

tblPlayer
ID
Last
First
Birthday
Address
Phone
Email
Father
Mother
Paid
Waiver

tblRoster
ID
Player
Jersey
Team
League
Season

tblStats
ID
Player
Goals
Assists
Penalties
PenaltyMinutes
League
Season
Game
Team

Both Player fields link back to the ID, Last, and First (names) fields in
the tblPlayers. I've been trying to populate the corresponding fields in
tblStats from tblRoster. That way, tblRoster could change periodically, and
tblStats could contain all the info for every game in each season.

What's confusing me is this. The duplicate fields serve different purposes.
Roster tells who's playing this season, while Stats holds the Players'
history over many seasons. If Roster holds the player-related info, how does
one update it without affecting the history stored in Stats?

My searching has turned up two kinds of information: how to do what you
already know you need to do, and abstract theory on proper database design.
Both are important, but I think that what I'm looking for falls in between
those two,

Thanks for your help!

 

[Steve]

Did you look at my response? The suggested tables store player stats over
the years. The key to understanding this is to recognize that RosterID
represents a specific member for a specific year and his stats for that year
are stored in TblPlayerStat.

Steve

 

[Steve]

You don't get it!! Arno R has no interest in the group. If he did, he would
spend his time helping posters rather than attacking me.

Steve

 

[Steve]

One of the data entry forms needed is to be able to enter TblRoster data for
a given year. This is done with a form/subform. The main form is based on
TblYear and the subform is based on TblRoster. A lot is accomplished doing
it this way. It removes the possibility of incorrectly entering HockeyYear
in TblRoster.
It gives you an efficient search for a selected year. It gives you a clean
way to delete all roster data for a selected year. Plus many other benefits.

Steve
(e-mail address removed)

 

[Steve]

<<Your proposed table structure suggests that tblRoster is a junction table
between tblTeam and tblMember>>

Your analysis is incorrect! Read my first post and the note for TblRoster.
Read my last post for the reason to have TblYear. RosterID represents a
specific member on a specific team for a specific year.

Steve

I don't know if you are responding to me or to the OP, but since you
responded directly to my posting I assume it is me.

Perhaps you didn't get that I was trying to get the OP to implement a
small
piece of the design for starters. I know that other forms are needed.
That
should be clear from my posting where I mentioned the next step.

I question the value of a one-field top-level table (Year). I would argue
for a Year or SeasonStart field or some such thing in the Roster table.
Your
proposed table structure suggests that tblRoster is a junction table
between
tblTeam and tblMember, which makes sense since a team rather than a year
has
a roster. It's hard to see how a table that links to tblYear and tblTeam
is
going to help anything. It's simple enough to restrict records to a
single
year.

One of the data entry forms needed is to be able to enter TblRoster data
for
a given year. This is done with a form/subform. The main form is based on
TblYear and the subform is based on TblRoster. A lot is accomplished doing
it this way. It removes the possibility of incorrectly entering HockeyYear
in TblRoster.
It gives you an efficient search for a selected year. It gives you a clean
way to delete all roster data for a selected year. Plus many other
benefits.

Steve
(e-mail address removed)

An ID field that is used as a linking field is like an EmployeeID
number.
You can move, change your name, go into a different departemnt, or

[quoted text clipped - 13 lines]

table, and then only to store the four-digit year without a separate ID
field)

 

[John... Visio MVP]

He does help out, but he has to waste time keeping you in line.

John... Visio MVP

 

[Ennead]

:
Did you look at my response? The suggested tables store player stats over
the years. The key to understanding this is to recognize that RosterID
represents a specific member for a specific year and his stats for that year
are stored in TblPlayerStat.

Yes, I did read your response. In my post I was merely indicating what I
had done up to this point. Both you and Bruce can clearly see to the heart
of my problem. I'm not sure which of you has the "better" approach, but I'm
going to try to do both of them to help clarify it for me. I'll let you know
when I get stuck again.

Thanks a lot to both of you!