Rodeo Contestant Tracking Database

rsburge
rsburge used Ask the Experts™
on
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.
for example...
17.000 = 10
17.973 = 9
18.000 = 8
etc.

Same principal applies to scored events with the highest score getting 10
79 = 10
75 = 9
70 = 8
etc.

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.



Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
You have posted what seems more like a request for assistance in building this entire Database, rather than one distinct question.

This would be better if taken as a series of separate, sequential, incremental questions, instead of one broadly based "Design" question...


JeffCoachman

Author

Commented:
First, I pay for my membership, so I thought I could ask basic design questions.

Second, if I knew how to make sure I was designing the database correctly, then I would have asked multiple questions rather than one overall "am I on the right page and any advice would be appreciated" question.

However if nobody wants to help then I will just withdraw the question and figure it out on my own.

And, by the way, it is a waste of my money to submit a question only to be told that I can't ask this question.
Top Expert 2011

Commented:
rsburge,

You said:
<< ... some clear advice on how to proceed would be extremely helpful as well. ... >>
I do feel that JeffCoachman was trying to do this.

What you are wanting is basically a membership database that also track what events your members attend.

The first thing you need to do is get your tables correct.  Please post a copy of your database with sample data in the tables. Nothing confidential.








11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
I have not started building the database yet...  I wanted to make sure my basic design plan was good before I started.  A few years ago, I had a database I started and when I finally discovered experts-exchange and asked a question, it strongly recommended that I basically scrap what I had and start all over.

If you think my basic table design plan is ok then I can start the database and upload a copy later today.

Our team did consider purchasing some software that was already designed for this purpose, but our team is not financially supported by the school district and we have very little money so we opted to build our own database.
Top Expert 2011

Commented:
<<If you think my basic table design plan is ok then I can start the database and upload a copy later today.>>

All you did was list some table names.  That really does not tell use anything about your  basic table design. For use to give you any advice on a you basic design you will need to include all the fields and relationships.  If you have this documented, like in Word or Excel, then you could post that.

How much do you know about data normalization theory?
To get some idea on a sample of building a membership kind of database, see below thread:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=54924

Or this link, which might gives you heads up on design:
http://msdn.microsoft.com/en-us/library/aa662176%28v=office.11%29.aspx

HTH,
Daniel
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<if nobody wants to help then I will just withdraw the question and figure it out on my own.
And, by the way, it is a waste of my money to submit a question only to be told that I can't ask this question.>
?
I was willing to help...
All I asked for was a more "targeted" question.

You can still post a "Broad" question like: "How do I design my tables?"
(as TheHiTechCoach asks)
This is fine.
...but what you originally posted was to broad to give a distinct "Answer"

As you know, the format of the site is basically Q & A.
Meaning one question, one answer.

We can still offer advice (as you are seeking), but the advice needs to be "Targeted"

For example
Q1: How do I design my tables?

Q2: How do I relate the tables?

Q3: How do I design Queries to...?

Q4: How doe I create forms to...?

Q5. How do I design Reports to...?
Note that none of these questions will garner a single, distinct "Answer", yet they are "targeted", so as to still fall under the umbrella of being one "Question".

All of these would be posted as separate questions.
This way you get he answer to one question, and can complete that portion of your project, then post the next question.

For example, ...until you have properly designed and relate the tables, a calculation to:
<show who has the most points overall across a single event by age group for all rodeos>
...can only be considered as a new question after the "Table Design" question has been answered.
Sound Fair?

Posting this as separate questions also allows experts to know how much time to dedicate to any one question.
It also adds value to the question(s) as repenting the logical progression of the overall design.


;-)

JeffCoachman

Author

Commented:
I can create tables, forms, queries, macros and reports and write sql queries and I can write some visual basic code (mostly trial and error until it works).

However...

1.  I don't know anything really about table normalization.  I am not a programmer I have just fiddled with Access and created databases mostly using the templates from Microsoft and then modified them to do what I need.  Or I have found answers on this site to help me accomplish what I need.

2.  I know absolutely nothing about how to make table relationships work properly.  Every time I have tried it, it doesn't work as I think I understand it is supposed to.

It appears that I really don't know enough to design this database at this time.  So I can either delete this question or split the points between those who have answered.  What do you all recommend?

Top Expert 2011

Commented:
I would recommend that you create a database with the table that you think you need with all the field. Create the relationships the best you can.  Post it here and we will help you get the design done correctly so that you can proceed.

At that point I would close this question.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
< I don't know anything really about table normalization.>
The you can start with some of the links to help you with the basic concepts:
http://support.microsoft.com/kb/209534
http://databases.about.com/od/specificproducts/a/normalization.htm

Here is a useful book:
http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201752840/ref=dp_ob_title_bk

...Then look into the links danishani posted.
All see the MS Access Membership Template:
http://office.microsoft.com/en-us/templates/membership-database-TC001018584.aspx

;-)

JeffCoachman


Author

Commented:
I am working on putting the database tables together now and will load the database as soon as I have it put together.

Author

Commented:
I am having a hard time trying to figure out designing the table fields correctly to make sure relationships work properly.

Attached is the database so far, but I think I have the tables a little off.  I don't fully understand what fields to make the primary key to make sure the relationships work properly.

I am not certain when I must have a primary key; I have tables that I think would be a one to many and I am not sure how to set up the primary key...
for example
tblContestants has CID as the primary key but the tblRelease does not have a primary key because a contestant could be listed more than one time, however I want to set up the relationship based on the CID.  I don't know if this is correct or not.

There is some data in some of the tables, but not all of the tables.

I have added a description for in the properties for each table and I have added a description for the field in each table.   I hope this is all helpful in determining if I have set this up correctly.

Below are my thoughts on the relationship of each table and hopefully I have set them all up correctly (I did not set them all up in the database; want to make sure everything is correct first).  I am also completely unsure of how to utilize referential integrity.

tblContestants one to many with tblContestantAge on the CID field
tblContestants one to many with tblContestantEvents on the CID field
tblContestants one to many with tblContestantResults on the CID field
tblContestants one to many with tblContestantRelease on the CID field
tblContestants one to many with tblFeesPaid on the CID field

I have a few other tables that I am not sure need relationships or not.

Please take a look and let me know your thoughts.
EHS-Rodeo-Team-Events-Database.accdb
Top Expert 2011

Commented:
That is great. If possible load some sample/test data into teh tables. at least two records be table.
Top Expert 2011

Commented:
<<but the tblRelease does not have a primary key because a contestant could be listed more than one time,>>
I think you might be confused about primary keys.

Every table should have a primary key. The simplest thing to do is use the Autonumber data type.  A primary key is used to uniquely identify the record.

I have your database downloaded.

Author

Commented:
Thank you for the information.  I thought that each table needed a key, but I got a little confused when looking at the memberships link from above.

I am working on getting the data added to each table and then uploading another db.  I will add a primary key to all of the tables that don't have one before I upload the db.
Top Expert 2011
Commented:
Assuming each event can have multiple results.

Rodeos > (1) have (many) > Contestants > (1) that are in (many)> Events > (1) with many (many) > Results

I would put people into a table called people.  All people go into this table.  This way a person can be a contestant in many rodeos.

You don’t need a check box for Release form received and Release Date. If there is no Release date then you now the Release form has not been received.


See attached

 EHSRodeoTeamEvents-HiTechCoach.accdb

Author

Commented:
Thank you for uploading the database.  I am going to look through it to make sure I understand everything and then I will post back with any questions or accept the answer.

Author

Commented:
Ok, I think I understand all of the tables but I'm not positive how the relationships work and how to make everything work in a form.

For example if I want a form for entering contestants in a rodeo, how would I make the form to show the contestant name and rodeo date and event and fee paid and the results that will also update the appropriate tables?
Top Expert 2011

Commented:
<<Ok, I think I understand all of the tables but I'm not positive how the relationships work and how to make everything work in a form.>>

It is like I previously stated the relationships are:
Rodeos > (1) have (many) > Contestants > (1) that are in (many)> Events > (1) with many (many) > Results

The (1) ... (many) above is defining the one-to-many relationships.  The relationship between the tables is very basic.  

At this point I never worry about how the forms will work. I believe if you start worrying about the form design now you can be influence to make poor table design choices because you are trying to make the form design easier. This usually leads to a poor design and only headaches down the line.

TIP: If you get the data design done correctly it really will make creating the forms and reports a lot easier.

At this point only focus on the tables. You really should ask about forms and report in another post/question once you have the table design done. Otherwise this will go on forever and it will be very difficult to award points to the people that have helps you.

The only thing I am not really clear about is if there will be multiple results (records) for a single event for a contestant at a single Rodeo?

Author

Commented:
I understand your comments on the table design.  I was just trying to enter data into the tables and couldn't fully understand how the data gets populated from one to the next based on the relationships.

It is possible for a person to have multiple entries in a single event during a single rodeo.  However for accounting and tracking purposes we would need to have a single contestant record for each entry for each person.

I also think it would be easier to maintain Release Form if moved to the poeple table since a person only needs to sign a release form once each year.  I thought it would be easier to enter it once in the people table rather than every time in the contestant table.  What do you think?
Top Expert 2011

Commented:
<<I also think it would be easier to maintain Release Form if moved to the poeple table since a person only needs to sign a release form once each year.  I thought it would be easier to enter it once in the people table rather than every time in the contestant table.  What do you think?>>

I think you are on the right track.

If it repeats the it need to have a separate record for each new release form. It would be a child/sub  table to the People that.  something like  tblPeopleRelases.


To manually enter data into the tables you will need to look in the parent table to get the primary key (autonumber) and enter it into the record as a foreign key relate the records.  When you build form you will use a sub form to show the related records. Access can be set to automatically enter the data to relate the record. The end user will never need to see the primary keys (autonumbers).

Author

Commented:
Thank you for all of the information.  One last question and then I will close this question.

<<Access can be set to automatically enter the data to relate the record. The end user will never need to see the primary keys (autonumbers).>>

How do I set this up?  If it is something I need to do before I start trying to build forms and reports, I want to make sure I do it now and correctly.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
If the tables are in a true One To many relationship, you can create a main/subform using the form wizard.
In this design the Main Form contains the "One" (Parent) records, and the Subform holds the "Many" (Child) records.
Moving to any of the Main records will display all of the related Child Records.
In this design, when you create a new child record, the corresponding Parent key is automatically inserted into the child record (whether you see it or not)

But remember that here you also need to be familiar with the concept of "Junction" (association, Intersection, .etc) tables...
Because you can have One Event with many Contestants, *AND* One Contestant can participate in many events....
...More to research...
;-)

JeffCoachman
Top Expert 2011

Commented:
rsburge
<<Access can be set to automatically enter the data to relate the record. The end user will never need to see the primary keys (autonumbers).>>

How do I set this up?  If it is something I need to do before I start trying to build forms and reports, I want to make sure I do it now and correctly.

Data entry in a relational database is rarely done directly into the tables.  It is handle with forms.

From my previous post.
When you build form you will use a sub form to show the related records. Access can be set to automatically enter the data to relate the record.

In the sub form control you set the Master and Child linking field properties. Access will then automatically add the data to create the relationship for he user. This way the user does not have to know anything about teh primary keys.

 Sub Form Linking
I put together a quick form for you.

 EHSRodeoTeamEvents-HiTechCoach2.accdb

Author

Commented:
Thank you for that information.

I have one last item I am trying to resolve with the tables.

We have event fees and admin fees.  

Right now I have one fee table that is linked to the Events table.

Do I need to have 2 additional tables to hold the admin fees?  If so, how do I make sure they are linked to all of the same tables that the existing fee table is linked to (it is all still the same as the sample database posted by HiTechCoach yesterday).
Top Expert 2011

Commented:
I will post another sample shortly.

Author

Commented:
Thank you.  I also noticed that a person can't have more than one record in the tblRodeoContestantsEvents table.  However, a person can have several events in one rodeo and they can have multiple entries for a single event for a single rodeo...  I have tried adding an auto number primary key and changing the relationship, but it isn't working.
Top Expert 2011

Commented:
Umm ... I will take a look at this.

<<I have tried adding an auto number primary key and changing the relationship, but it isn't working.>>

Interesting ... All the table already had a primary key.
Top Expert 2011

Commented:
My bad. I did not look close at your tables.

I have made some adjustments to the tables and add a new table.

See is this one makes more sense.


EHSRodeoTeamEvents-HiTechCoach3.accdb

Author

Commented:
Thank you, I will see if I understand it all and then post any clarifying questions.  I do really appreciate your help.  I tried to figure it out on my own, but seemed to mess it up. :)

Author

Commented:
Well, I managed to completely lose my work in the database I was working in (not sure what happened except I must have not saved the database to my HD after I opened it from this page), so I am going to close this question and open a new one if I have any additional questions.

Thank you for all of your help!
Top Expert 2011

Commented:
You're welcome.

TIP: Make a backup or ZIP before opening the database each time.


Author

Commented:
I usually do make back-ups but must have forgotten this time.  :)  It has been one of those weeks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial