A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Duplicate Fields Problem in My Tables



 
 
Thread Tools Display Modes
  #11  
Old February 17th, 2010, 01:46 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Duplicate Fields Problem in My Tables

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)
  #12  
Old February 17th, 2010, 06:27 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Duplicate Fields Problem in My Tables

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




"BruceM via AccessMonster.com" u54429@uwe wrote in message
news:a3c497b76f700@uwe...
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)



  #13  
Old February 17th, 2010, 07:57 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Duplicate Fields Problem in My Tables

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.


Steve wrote:
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


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)


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201002/1

  #14  
Old February 17th, 2010, 08:25 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Duplicate Fields Problem in My Tables

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


"BruceM via AccessMonster.com" u54429@uwe wrote in message
news:a3c7d520d1842@uwe...
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.


Steve wrote:
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


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)


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201002/1



  #15  
Old February 17th, 2010, 09:26 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Duplicate Fields Problem in My Tables

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

  #16  
Old February 20th, 2010, 07:39 AM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default Duplicate Fields Problem in My Tables

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

John... Visio MVP
"Steve" wrote in message
...
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



  #17  
Old February 20th, 2010, 11:34 PM posted to microsoft.public.access.tablesdbdesign
Ennead
external usenet poster
 
Posts: 4
Default Duplicate Fields Problem in My Tables


"Steve" wrote:
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!
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.