Copying field to another table when y/n is selected

[Chris Oneal]

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

[Seth Schrock]

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

tblStudents
StudentID_pk
StudentName
...

tblCategories
CategoryId_pk
Category
...

tblTries
TryId_pk
Try

tblScores
ScoreId_pk
StudentId_fk
CategoryId_fk
TryId_fk
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.

[Chris Oneal]

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.

[NeoPa]

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.