New DB project

[Guest]

I know that the DB design is one of the most important steps in creating a
project, that is why I am asking for your help. I am novice at using Access,
but I do have programming experience, mainly from COBOL.

My new project. I am looking at creating a DB to track a sporting event
league. There are three leagues per year, sometimes different teams and
competitors. I would like to keep multiple years worth of data in the DB
that could be accessed at any time.

What I have so far.

ClubTable:
ClubID – Primary Key
ClubName
ClubAddr1
ClubAddr2
ClubCity
ClubState
ClubZip
ClubPhone
ClubLeagueID – This will determine the current league

LeagueTable:
LeagueID – Primary Key
LeagueDesc
LeagueWeek
LeagueDate
For each league there will be 14 weeks of scores. I will need to
Keep track of the week and the date.

TeamsTable:
TeamID – Primary Key
TeamDesc
TeamSponsor
TeamLeagueID

ScoresTable:
ScoresID – Primary Key
ScoresLeagueID
ScoresTeamID
ScoresMemberID
ScoresWeekID
ScoresSlow
ScoresTimed
ScoresRapid
There will be 14 scores for each competitor for each league

ClassTable:
ClassID – Primary Key
ClassDesc
ClassShort
ClassMin
ClassMax

MemberTable:
MemberID – Primary Key
MemberLastName
MemberFirstName
MemberAddr1
MemberAddr2
MemberCity
MemberState
MemberZip
MemberPhone
MemberEmail
MemberStartAvg
MemberNewCompetitor
MemberNRA
MemberCaliber
MemberLeagueID
MemberClassID
MemberTeamID

Now, my goal is to have one main data entry screen that will find all the
teams in the current league and sort them by TeamDesc, then find all members
of that team and sort them by MemberLastName, MemberFirstName, after that I
will need to display the 14 dates that make up the league and have to enter
the ScoresSlow, ScoresTimed, ScoresRapid for each competitor for each week.
Scores will be entered weekly so that we may calculate the team placement.

I have been playing with forms & sub-forms and have had some luck on getting
the correct teams and members to show up on the sub-forms, my problem is how
can I display the 14 dates and then display / enter the 14 weeks of scores.

Any and all help is appreciated if anyone is up to the challenge!

Thanks,
Jeff
(e-mail address removed)

 

[Roger Carlson]

It's impossible to say, because you haven't given any Relationships between
the tables. For instance, is it true that:

Each League is composed of One or More Clubs
AND
Each Club belongs to One and Only One League

This will necessitate a different design than one in which a Club can belong
to many Leagues. This information is as important as the table/field
information and you have to have it for every relationship. In essence,
you're asking for a critique without explaining the business rules.

You might want to take a look at the Tutorials Section of my website:
http://www.rogersaccesslibrary.com/TutorialsDesign.html

On it I have several step-by-step tutorials based on the principles in
"Database Design for Mere Mortals" by Michael Hernandez (you should get the
book too), that should make this process easier.

You might also find a pre-built data model here:
http://www.databaseanswers.org/data_models/index.htm

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Roger, Thank you for your reply. As for the design, there is only one club,
but multiple teams, multiple competitors. There can be multiple teams per
league, but one competitor can only be assigned to one team during one
league. The club table is used more as a control record to show which is the
current league. From here, the queries will find all of the information from
that league.

Thank you for the references, I will be looking into them next.
Jeff