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)

 

[BruceM via AccessMonster.com]

I do not agree with your posting. If Steve starts advertising you can reply
then, but a pre-emptive attack is as unethical as advertising.

 

[BruceM via AccessMonster.com]

To respond to just a few of your points, there should be no need to store
duplicate information. That is where linking fields are used, which is a big
difference from Excel. If Stats is related to a Game table, all you need to
store in the Stats table is the linking field. One of the difficulties is
that there are a lot of moving parts in a project such as this one. If you
post what you have so far it may be possible to help steer you in the right
direction.

 

[BruceM via AccessMonster.com]

I know what you said. I read your full posting, and I responded as I did. I
repeat that the time to confront him is when he actually solicits. There
have been plenty of opportunities for that. Pre-emptive attacks tend to come
across as personal rather than in the interest of the group.

 

[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

 

[BruceM via AccessMonster.com]

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 whatever.
Through it all your payroll and other history is linked to your EmployeeID
only. Most likely they don't store your name and so forth every time you get
paid. They store just your EmployeeID, and link to the main Employee table
to display your name and other information as needed. Likewise player
information exists in the Players table only. Any other time you store the
Player information you store the single ID field.

Steve showed a table structure that is one way to proceed, given certain
assumptions (although I see no need for a Years table except as a lookup
table, and then only to store the four-digit year without a separate ID field)

 

[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)

 

[BruceM via AccessMonster.com]

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)

 

[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)

 

[BruceM via AccessMonster.com]

Your analysis is incorrect!

Then explain to me the reason for linking fields to tblTeam and tblMember.

Oh, you could have a one-field top-level table for the Year, but it is a
stretch at best to argue that a Roster is an attribute of a Year. From a
user interface point of view, would you have the user go to a Year record,
then select the Team? That would be a pointless extra step. I can think of
several ways to limit the recordset to the current year, or otherwise prevent
entries for the wrong year.

<<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.

[quoted text clipped - 37 lines]

 

[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!