Solved

Best way to get data into a database

Posted on 2017-05-09
12
67 Views
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.
Database_design_question.docx
0
Comment
Question by:agwalsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
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.
0
 
LVL 37

Accepted Solution

by:
PatHartman earned 250 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:
A-Z
a-z
0-9
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.
ManyToManyAXP.mdb
0
 

Author Comment

by:agwalsh
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 :-)))))
0
Upcoming Webinar: Securing your MySQL/MariaDB data

Join Percona’s Chief Evangelist, Colin Charles as he presents Securing your MySQL®/MariaDB® data on Tuesday, July 11, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 

Author Comment

by:agwalsh
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?
0
 
LVL 37

Expert Comment

by:PatHartman
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.
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 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:

RaceID
CyclistID
ContractID (if needed)
StageID
Time
Result

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.
0
 

Author Comment

by:agwalsh
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.
0
 
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.
0
 
LVL 37

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:agwalsh
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?
team--1-.accdb
0
 
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.
team--1-.accdb
0
 

Author Closing Comment

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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

690 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