[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Best way to get data into a database

Posted on 2017-05-09
Medium Priority
Last Modified: 2017-05-19
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.
Question by:agwalsh
  • 5
  • 4
  • 3
LVL 85
ID: 42126981
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.
LVL 40

Accepted Solution

PatHartman earned 1000 total points
ID: 42127048
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.

Author Comment

ID: 42127073
@Scott McDaniel...the idea would be to easily allow analysis of the race and the various stages..so 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 :-)))))
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.


Author Comment

ID: 42127091
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 this...so that the user could pick the most current contract?
LVL 40

Expert Comment

ID: 42127191
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.
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 42127320
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.

Author Comment

ID: 42127361
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.
LVL 85
ID: 42127527
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.
LVL 40

Expert Comment

ID: 42127642
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.

Author Comment

ID: 42133305
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?
LVL 85
ID: 42133418
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.

Author Closing Comment

ID: 42141989
As always brilliant help that really clarified things. Both of you..priceless :-)

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question