New DB project

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HD87glide - 19 Jul 2005 15:54 GMT

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
Hd87glide@hotmail.com

Reply to this Message

Roger Carlson - 19 Jul 2005 19:45 GMT

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

Signature

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

> 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,
[quoted text clipped - 85 lines]
> Jeff
> Hd87glide@hotmail.com

Reply to this Message

HD87glide - 19 Jul 2005 20:01 GMT

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

> It's impossible to say, because you haven't given any Relationships between
> the tables. For instance, is it true that:
[quoted text clipped - 115 lines]
> > Jeff
> > Hd87glide@hotmail.com

Reply to this Message