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
  #1  
Old February 14th, 2010, 10:59 PM posted to microsoft.public.access.tablesdbdesign
Ennead
external usenet poster
 
Posts: 4
Default Duplicate Fields Problem in My Tables

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!

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

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


"Ennead" wrote in message
news
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!



  #3  
Old February 15th, 2010, 09:16 AM posted to microsoft.public.access.tablesdbdesign
Stop$teve
external usenet poster
 
Posts: 76
Default Duplicate Fields Problem in My Tables


"Steve" schreef in bericht ...
Try these tables:


snipped

No need to answer ANY questions here $teve...

No-one wants you here... no-one needs you here...

OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 11.000 pageloads... it's a shame !!)

For those who don't 'agree' with this mail , because $teve was 'helping' with his post...
We warned him a thousand times... Sad, but he is not willing to stop advertising...

He is just toying with these groups... advertising like hell... on and on... for years...
oh yes... and sometimes he answers questions... indeed...
and sometimes good souls here give him credit for that...

== We are totally 'finished' with $teve now...
== Killfile 'Stop$teve and you won't see these mails....

Arno R


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

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.

Stop$teve wrote:
Try these tables:


snipped

No need to answer ANY questions here $teve...

No-one wants you here... no-one needs you here...

OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 11.000 pageloads... it's a shame !!)

For those who don't 'agree' with this mail , because $teve was 'helping' with his post...
We warned him a thousand times... Sad, but he is not willing to stop advertising...

He is just toying with these groups... advertising like hell... on and on... for years...
oh yes... and sometimes he answers questions... indeed...
and sometimes good souls here give him credit for that...

== We are totally 'finished' with $teve now...
== Killfile 'Stop$teve and you won't see these mails....

Arno R


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

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

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.

Ennead wrote:
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!


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

  #6  
Old February 15th, 2010, 02:53 PM posted to microsoft.public.access.tablesdbdesign
Stop$teve
external usenet poster
 
Posts: 76
Default Duplicate Fields Problem in My Tables


"BruceM via AccessMonster.com" [email protected] schreef in bericht news:[email protected]
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.


As I said:
oh yes... and sometimes he answers questions... indeed...
and sometimes good souls here give him credit for that...


sigh
IMO we should ALL be attacking $teve in an attempt to finally get rid of him...

Regards, Arno R


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

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.

Stop$teve wrote:
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.


As I said:
oh yes... and sometimes he answers questions... indeed...
and sometimes good souls here give him credit for that...


sigh
IMO we should ALL be attacking $teve in an attempt to finally get rid of him...

Regards, Arno R


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

  #8  
Old February 17th, 2010, 04:10 AM posted to microsoft.public.access.tablesdbdesign
Ennead
external usenet poster
 
Posts: 4
Default Duplicate Fields Problem in My Tables

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

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


"Ennead" wrote in message
...
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!



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

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" [email protected] wrote in message
news:[email protected]
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.

Stop$teve wrote:
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.


As I said:
oh yes... and sometimes he answers questions... indeed...
and sometimes good souls here give him credit for that...


sigh
IMO we should ALL be attacking $teve in an attempt to finally get rid of
him...

Regards, Arno R


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



 




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 03:30 PM.


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