Best way to get data into a database

I've set up a database structure as per the attached document.  It's intended to track a cycling event e.g. Tour De France. So it's got a table for a team, for cyclist (and their contracts), for races, for stages in the race and then a results table.  If there's a better way to design it,let me know. My question is this...what would be the best way to get this data into the tables..this is what I am thinking at the moment..
Form for the team by itself.
For each cyclist a form with a sub-form for their contracts (based on the Cyclist contract table)
Table for each race with a subform (based on the RaceStage Table)
I then get bogged down in the results...what's the best way to capture the results? Thanks as always.
Who is Participating?
PatHartmanConnect With a Mentor Commented:
Name should be FirstName and LastName to provide flexibility.  Once you mush the parts of a name, it is almost impossible to separate them due to the variations that are possible.  If you stick with a mushed field, do not name it "Name".  Name is a reserved word because it is the name of a property and will cause considerable problems in code.  For example, is Me.Name the name property of the form or is it the Name control?  Be careful to avoid reserved words that are functions also such as Year, Date, and Time.  names like Description (or maybe it's Desc) will cause trouble because they are reserved words for SQL Server.  If you think there is a possibility a word might be reserved, qualifiy it - RaceDesc, RaceDT,  Also, NEVER use embedded spaces or special characters.  The rules for naming object are:
Underscore "_"
Start names with a letter and limit to 32 characters and that should keep you from running afoul of rules in most computer languages.
I use CamelCase, others like the_underscore as a way to form names.

In the Results table, you should use ContractID rather than CyclistID.  That gives you The contract of the cyclist at the time of the race without having to figure it out based on date.

I've attached a database with a sample many-many relationship to show you how to construct forms that look at the relationship from each direction.  So you would see cyclist to team to race and race to cyclist.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you capturing results for a Race, or for a RaceStage, or both?

If both, would a "Race" be considered the sum of all RaceStages for that Race?

Either way, you would need a form where you'd select the Race, and then the Cyclist (and then the Stage, if you're entering results by STage), and then enter the Results you need. Race, Cyclist and Stage would be dropdowns based on their respective tables.
agwalshAuthor Commented:
@Scott McDaniel...the idea would be to easily allow analysis of the race and the various what would be required here would be a standalone Results form that would allow entry of the Race/Cyclist/Stage and then the actual time. Is that correct...and that would be mapped to the Results table?
@PatHartman- thanks for that excellent advice - yes, you are right of course should be FirstName and Surname rather than the two names together and I take on board your comments on the reserved words and naming. Thank you both so much :-)))))
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

agwalshAuthor Commented:
Another thought -@Scott McDaniel - I presume I could set up the form so that when someone picks a race from the list, I could link the stage part of the form to it via its combo box source so that it only picks the stages for that race? What would be the best way to link the Contract ID in that the user could pick the most current contract?
You would use a combo box to link to the race.  Sort the list descending by date so the newest races are on top and you don't have to scroll.
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
So you want to associate a "contract + cyclist" to a Race or RaceStage? Is it possible for a cyclist to have more than one Contract for a single Race, or RaceStage?

Will you know the RaceStages prior to the Race? In other words, when setting up a Race, would you enter (a) the Race information and then (b) the RaceStage information for that race (assuming a Race has more than one RaceStage)? If so, then you could design a form where you would select Race, and then select the Cyclist (or Cyclist + Contract), and your subform would show the RaceStages for that Cyclist for the Race. For situations like this, you might consider a temporary table to be used when you select a Race and Cyclist. That temporary table would be something like this:

ContractID (if needed)

The reason for that would be so you could present the user with all the Stages of the Race for the Cyclist, and allow them to fill in the information. When saving the data you'd introduce code to cycle through each row of the table, and write back that data to the Results table if the row has information entered by the user.

Of course, you could simply show the user a subform in Datasheet or Continuous view, and allow them to select the Stage from a dropdown. The Temporary table idea sometimes provides a little "cleaner" interface, at the expense of more work on your part.
agwalshAuthor Commented:
The cyclist will only have ONE contract with one team for a specified period.. Yes, the stages would be known beforehand.  I like the idea of creating a form for results
StageID (FK)      Integer
RaceID  (FK)      Integer
CyclistID (FK)  Integer
ContractID (FK)      Integer
Time      time
Result      Number
Have the StageID/RaceID/ContractID as combos, using the PK, but using the combo feature to show the actual stage name, race name, cyclist name etc, then the user to manually enter the time.  How does that sound? The position/result could be done via a query to show that.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The cyclist will only have ONE contract with one team for a specified period
Then I'm not sure why you would include the Contract as a foreign key field in the Results. The Contract is an attribute of the Cyclist, not the Race, Stage, or Result.

Other than that, it would seem that you can either (a) use the temporary table idea to present the user with a form that lists all the Stages of the selected Race or (b) create a form with combos where the user would select the Race, then the Stage, then enter the data for that stage ... the user would then select the next Stage (from a dropdown), and enter the results, and so forth. At that point, it's really just a matter of personal preference.
You don't need both the CyclistID and the ContractID since you can derive the CyclistID if you have the ContractID.  You cannot however derive ContractID unless you also have a date and that is why I suggested using it to link the Cyclist to the race.

The assumption for a race is that the cyclist will complete all phases so at the start of the race, I would run an append query that selects all the stages for the race and appends them to each entrants results table.  Then as the cyclist completes a stage, his result can be entered.  Doing this append ahead of time will minimize the potential for missed entries.  If the Cyclist drops out for some reason, you would mark the remaining phases as incomplete.

Lay out some data entry forms in Excel.  Put an outline around what you see as a subform and we'll help you to optimize the data entry.
agwalshAuthor Commented:
hi Folks, I'm uploading a database that has some dummy data in it. Couple of questions..
1. In the Results form, this is what I have done to show the Race name rather than the Race ID - set the column width to 2, set the first column to 0. Is that good enough?
2. How can I set up the form so that the stages can be filtered to only show stages for a particular race for the user and that its refreshed every time they update it?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In the Results form, this is what I have done to show the Race name rather than the Race ID - set the column width to 2, set the first column to 0. Is that good enough?
Yes, that's the correct way to show the "human" value, which still storing the correct Primary Key value of the selected record.

You should use Unbound combos to filter for specific criteria. In your Results form, you should have combos (perhaps in the Form Header) that are Unbound (i.e. no ControlSource), and you would use those to filter. I've added this to the database and uploaded here.
agwalshAuthor Commented:
As always brilliant help that really clarified things. Both of you..priceless :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.