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.


Steve wrote:
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]
table, and then only to store the four-digit year without a separate ID
field)

--
Message posted via http://www.accessmonster.com