Flower Show results tally (1 Viewer)

Mr_Si

Registered User.
Local time
Today, 04:50
Joined
Dec 8, 2007
Messages
163
Hi all,

I am making a database for a local flower show (run by my Father in Law). I have made quite a lot of progress and a lot of the functionality. However I am now at the point where I need to transform the results of 1 - 4th place into scores and tallying them up.

Basically,
1st place = 5 points
2nd place = 3 points
3rd place = 1 point
4the place = 0.5 points

There are awards for different categories, some for best exhibit in class and others for most points in a section of classes. There is also prize money (small amounts) for these results.

Many people can enter many classes.

I need a way of tallying up these results in to a table and then calculating totals etc


Last year, we had a spreadsheet with each entrant across the top and each class down the side and just made a note of "5", "3", "1", or "0.5" and then used Excel formulas to calculate the totals and see who won which Award as a result of their scores.


However, in this database, I'd like to be able to store the scores in a table and then reference them on a form or a report to print out who gets what and keep it as a reference per year.


What would be the best approach of going about this?

I just need some pointers in the right direction.

Many thanks in advance,

Simon
 

tehNellie

Registered User.
Local time
Today, 04:50
Joined
Apr 3, 2007
Messages
751
Depends on the rest of the structure in some respects.

tblPlacePoints might be appropriate
Code:
PlacepointsID  Points     Place
1                5        1St
2                3        2nd
3                1        3rd
4                0.5      4th
5                0        Did not place

Allocate one of those to each entry as appropriate

if you want to change the scoring, you just do it in the PlacePoints table.

You can query and calculate each entrants points based on the values in that table.
 

Mr_Si

Registered User.
Local time
Today, 04:50
Joined
Dec 8, 2007
Messages
163
Yes, I can see what you've done there.

Sorry, I knew I had failed to mention something. I've actually already structed it the other way around...

I have it so that there is a table called "Results" and the fields are as follows
Code:
idsResultID
lngzShowDate
lngzClassID
lngzFirstPlace
lngzSecondPlace
lngzThirdPlace
lngzFourthPlace
lngzHighlyCommended
lngzCommended
The 6 results fields are lookups to the Entrant, so you select the entrant for the relevant result, rather than the other way around.


Sorry.
 

DavidAtWork

Registered User.
Local time
Today, 04:50
Joined
Oct 25, 2011
Messages
699
If you do it the way you're suggesting above, you will require some quite complex querying to be able to calculate any individual's overall score. Perhaps your table design could be like:
tblClass
classID_PK
class_description
class_cat_FK (to categories)
class_section_FK (to sections)

tblCategories:
categoryID_PK
category_description

tblSection:
sectionID_PK
section_description

tblEntrant:
entrantID_PK
entrantSurName
entrantForeName


tblExhibitEntries:
exhibitID_PK
classID_FK (to class)
entrantID_FK (to entrant)
exhibitDate

tblResults:
resultID_PK
classID_FK (to tblClass)
result_placeID_FK (to place lookup)
resultDate
prizeMoneyAmt

tblPlaceLU:
placeID_PK
place_description
place_points

In these tables I've only included what appears to be the minimum detail (fields) but with this type of structure should give you all you need to record your classes, sections, entrant details, exhibit entries and results. From these it will be straightforward to create queries to report on any of the items you specified above allowing you to group by category, section or whatever
David
 

Users who are viewing this thread

Top Bottom