By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,913 Members | 1,356 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,913 IT Pros & Developers. It's quick & easy.

Copying field to another table when y/n is selected

P: 7
I am a PE teacher and am trying to create a more efficient way of keeping score for our Cup Stacking tournaments. I have started the database, but am now a little stuck. Cup stacking has three categories and I will need to keep track of three times from each student. The categories are the 3-3-3, 3-6-3, and the cycle. They get three tries in each. I have created a table with student info (ID, name, etc.) and I put yes/no boxes for them to choose the games. They can only choose two. If they check the 3-3-3 box, for example, I want their ID and name to be copied to the 3-3-3 table. I would also like them to be able to choose only two boxes, if possible. I have also created a 3-3-3 table with time1, time2, and time3. Thanks
1 Week Ago #1
Share this Question
Share on Google+
3 Replies

Seth Schrock
Expert 2.5K+
P: 2,943
I highly recommend looking at Database Normalization and Table Structures. Basically you need a table each for students, categories, and tries. You then need another table to join those tables together that also has a score field in it. For example
Expand|Select|Wrap|Line Numbers
  1. tblStudents
  2. StudentID_pk
  3. StudentName
  4. ...
Expand|Select|Wrap|Line Numbers
  1. tblCategories
  2. CategoryId_pk
  3. Category
  4. ...
Expand|Select|Wrap|Line Numbers
  1. tblTries
  2. TryId_pk
  3. Try
Expand|Select|Wrap|Line Numbers
  1. tblScores
  2. ScoreId_pk
  3. StudentId_fk
  4. CategoryId_fk
  5. TryId_fk
  6. Score
This may seem like a bit of a hassle, but having the data stored this ways allows you to fully utilize the data that you have. For example, it is very easy to return the highest score, or the average score, or see if most students do better or worse on their second try compared to their first. You save yourself many headaches when you store the data properly.

However, you may want to display the data in a more user-friendly manner. There are many ways to do this, but the first that pops into my mind would be to have a form listing your students with a subform allowing to select which categories they will be a part of (based on tblScores). You could then open another form which will allow you to enter the scores for each try (again based on tblScores). Again, this is just one method of displaying the data and isn't necessarily the right way. Only you can determine how the user should interact with the data.
1 Week Ago #2

P: 7
Thanks for the reply. I have made the form and table for the students to "register" their entry into the tournament. They are to enter their Student ID, First name, last name, school, and then select with the y/n box the games (3-3-3, 3-6-3, or cycle).I have attached an example of what the time sheets look like. I want to find a way where it will populate a table of ex. 3-3-3 when the student selects the 3-3-3 check box. In your example you have TryID, will i create for example 3-3-3Try1, 3-3-3Try3, 3-3-3Try3, 3-6-3 Try1, ...?, and I don't know what how I could create a primary key on that one? Sorry, I have kids coming in now. I have until next Oct. or Nov. to get this done. Thanks a lot for your help.
Attached Files
File Type: docx cupstacking time sheet.docx (12.5 KB, 4 views)
1 Week Ago #3

Expert Mod 15k+
P: 31,530
Hi Chris.

May I suggest that your next step should be to read the linked article then reply in such a way as to indicate you've understood the contents, at least to a basic level.

To continue any sort of discussion on this without that very fundamental pre-requisite would be a total waste of everybody's time - yours included.

As a general rule it always makes good sense in such written 'conversation's to include clear indications of an understanding of all intevening posts so that both sides of the conversation can understand what it is they're dealing with.
1 Week Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.