Best method to storing/calculating contest scores

[anobes03]

What is the best method to create a database that can store contestants and
scores for each contest they participate in??

 

[John W. Vinson]

What is the best method to create a database that can store contestants and
scores for each contest they participate in??

Using a set of properly normalized tables.

At a glance, I'd say you need a table of Contestants (PersonID, LastName,
FirstName, other contact information), Contests (ContestID, information about
the contest) and Entries (PersonID - link to Contestants, who entered;
ContestID - which contest they entered this time; other fields for the score
or other outcome, about which we know nothing).

 

[anobes03]

Thanks here is what i got:

Tables:

Contestants - ContestantsId,(Contact INFO)
Judges - JudgeID (Contact info)
Category - Cat.ID Name of Cat.(Ex. Female Adult, Male Teenager)
Competetion - Comp.ID Name of Comp.(Comedy,Fashion,etc...)
Entry- EntryID,CompID,CategoryID,ContestantID,JudgeID
Results - EntryID,Score

Do you see any potential holes? The database will hold couple hundred entries.

Need reports to run for 1st,2nd,3rd Place. Also reports to run for different
score results per entries.

Also I need some ideas on Forms to build, for entering the scores. Thanks!

 

[John W. Vinson]

Thanks here is what i got:

Tables:

Contestants - ContestantsId,(Contact INFO)
Judges - JudgeID (Contact info)
Category - Cat.ID Name of Cat.(Ex. Female Adult, Male Teenager)
Competetion - Comp.ID Name of Comp.(Comedy,Fashion,etc...)
Entry- EntryID,CompID,CategoryID,ContestantID,JudgeID
Results - EntryID,Score

I presume there are CatID fields in Contestants? perahps in Competitions too
(i.e. do you have Fashion competitions for Female Seniors?)

Do you see any potential holes? The database will hold couple hundred entries.

You're the one to see what's missing... because you know your competitions far
better than I do, and you can see what's needed. One good thing is that if you
find yourself saying "Gee I wish we could..." after the first week you can
always add new fields or even new tables.

I don't see anything in your design that would create obstacles. Looks good!

Need reports to run for 1st,2nd,3rd Place. Also reports to run for different
score results per entries.

Also I need some ideas on Forms to build, for entering the scores. Thanks!

A Form based on Competition, with a Subform based on Entry, with a sub-subform
based on Results. This assumes that a given entry will have more than one
score in a given competition; if not, just drop the Results table and add a
Score field to Entry. If so, you may want to add another field or two to
Results to identify what *kind* of score (multiple judges? audience response?
again, you'ld know better than I).

Calculating first - third place would be easy with a Top Values query sorting
by score (or sum of scores for an entry, if that's appropriate).

 

[anobes03]

Wow thanks your advice helped me alot!! The database is almost complete but,
The only problem I'm having is there is about 25 judges, and 700 entries, and
Every Judge will score every entry (give or take a few). What will be a way
to handle this??

 

[John W. Vinson]

Wow thanks your advice helped me alot!! The database is almost complete but,
The only problem I'm having is there is about 25 judges, and 700 entries, and
Every Judge will score every entry (give or take a few). What will be a way
to handle this??

A many-to-many relationship (each competition has many judges, each judge
judges many competitions) needs a Judging table with fields for CompetitionID,
JudgeID, and fields for the outcome of that judging. Each incident of judging
a competition will be a new record in this table.

The user interface might be a form - based on either Competitions or on
Judges, whichever suits your procedure best - with a Subform based on Judging.

 

[anobes03]

Right now I have this for the scoring:

Results tbl- JudgeID,EntryID,Score
Entries tbl - EntryID (Auto) ,CompetetionID,ContestantID

I need a way to apply all the judges to all the entries in the results table
BEFORE the contests are physically performed. Right now I'm just manually
inputting them in the table(and there is a total of 20,000 records!) I just
feel I'm doing something wrong here! There has to be a more simplier way then
this.

 

[anobes03]

Should I move the score field to the new table??

It seems you need to change the results table and add another table. In the
results table remove JudgeID and add a new field ResultID (Auto). Then
create a new table:

TblResultJudge
ResultJudgeID
ResultID
JudgeID

Steve
(e-mail address removed)