Hello - I am on the board of our local High School Rodeo team and as the only person associated with the team with any Access experience, I have been elected to create a database that will track the rodeo contestants and their fees and results.
I have created a few access databases, but have to admit, I feel this one is a little out of my league.
Ultimately we need to track the entries and results for each contestant for each rodeo and then provide various reports including the standings and accounting info for each rodeo and then overall.
I suspect we need the following tables, but I am not sure what other tables we need or how to make sure I have the proper relationships set up.
Contestants (to track name and age group)
Age Group (5 different age groups to choose from)
Release (to identify who has signed a release form)
Events (10 different events to choose from)
Rodeos (dates/locations of each rodeo)
Results (to track the results per event per contestant per rodeo; results are either decimal or a whole number; also need to include penalty field(s) which is a whole number and then a points field which will be populated based on the score or time)
Fees Collected (to track the fees collected per contestant per event per rodeo and identify if they are cash or check and if check, need the check number)
Points will be calculated as follows...
for timed events... by rodeo, by event, by age group, by contestant... lowest time gets 10 points and then backwards down to 1 anything below that is 0 points.
17.000 = 10
17.973 = 9
18.000 = 8
Same principal applies to scored events with the highest score getting 10
79 = 10
75 = 9
70 = 8
Then we also need to calculate overall standings for the following...
to show who has the most points overall across all rodeos and events
to show who has the most points overall across a single event for all rodeos
to show who has the most points overall across a single event by age group for all rodeos
and then a few others as well
I can provide the fields we know we need per table, but I want to make sure I am on the right track.
If anyone has a sample database, that would be huge, if not some clear advice on how to proceed would be extremely helpful as well. I am most concerned about storing this data in the approprtiate number of tables with the appropriate relationships so that it can all be accessed and reported on correctly.
We will want to use a form for entering data and then have sub forms or multiple forms to see things like the fees and standings per contestant.
I hope all of this makes sense.
Thank you in advance for any help you can provide.