Use a value from a field as stored value in another field

[m17347047711116]

I have a parent form that has the following
[Category],[Event],[Name]

In the sub form i am also storing the same values
[Category],[Event],[Name] & [Contestant ID], [Score1],[Score2]

I do not want to enter the
[Category],[Event],[Name] each time i enter a record into this sub form i would like the value that is in the parent form to be picked up and stored as the values for these fields.

Is there a simple way of doing this?

 

[RuralGuy]

This data should *not* exist in more than one table. Use a query to make the information visible in the SubForm.

 

[m17347047711116]

I have the table that contains the
[Judge Name],[Event],[Category]

Now i want to enter the scores for each contestant

[Judge Name],[Event],[Category],[Contestant Name],[Score1],[Score2]

I would love not to duplicate this info but i cant figure out how to enter the scores[1] , [2] for each contestant in a [Event], [Category] and link those scores to a specific judge as there are multiple judges for each [Category].

 

[RuralGuy]

I think the problem is in the normalizing of the tables. Let's see if we can reason this out correctly. Normalized the data almost always leads to the forms being a snap.

We have to think about the relationship each piece of data has with the other pieces. One-to-one relationships can usually go into the same table. One-to-many relationships belong in separate tables. You will have to correct my assumptions since only you know what the terms actually mean.

So far I see fields for the following entities:

.Judges
   .IDNumber
   .Name
.Events
   .EventID
   .Name
.Categories
   .CatagoryID
   .Name
.Contestants
   .IDNumber
   .Name
.Scores

If we had a table for each of these "entities" what would be the names of the fields? I started Judges and Contestants. Try and finish the others and correct whatever errors you see so far.

 

[m17347047711116]

Reply to table setup

..Judges
IDNumber
Name
..Events
EventID
Name
..Categories
CatagoryID
Name
..Contestants
IDNumber
Name
..Entry Information
IDNumber
Name
Event
Category
Class
Remarks
..Scores
IDNumber
Judge Name
Contestant Name
Symetry
Round

This is where the problem comes in, as a judge i want to see a form that has all the contestants for a specific category on the left and next to their names i want to enter the scores for [Symetry] & [Round] but i do not want to enter duplicate information over and over.

Thank you very much for your help, I have over 20 hours into this problem, i will learn to over come it.

 

[RuralGuy]

I think you just lost me a bit. BTW this is the bikini contest isn't it? Let's take the issues one at a time.

...Entry Information
.IDNumber
.Name
.Event
.Category
.Class
.Remarks

What is an Entry, Event, Category, Class. How do they relate to each other?

...Scores
.IDNumber
.Judge Name
.Contestant Name
.Symetry
.Round

What is a round?

Those answers should get us to the next layer of questions.

 

[m17347047711116]

Reply to definitions

[Event] is a title "Mrs Canadian National"
[Category] is "Fitness" or "Muscle"
[Class] "100lb to 112lb"
[Symetry] "would be a judges score for the bodies side to side symetry"
[Round] "would be the judges score for the bodies roundness vs's cut"
How the above relate to each other is this a contestant will be enrolled in multiple [Categories] in the contest as they may compete in fitness and muscle categories ect.. There will be many judges for each [Category]. The judges will need to enter a value for [Symetry]&[Round] for each contestant these values need to be linked to a specific [Contestant Name]&[Category]

Hope this helps, you are the only resource i have right now

 

[RuralGuy]

We're making progress. How soon do you need the completed project?

 

[m17347047711116]

I am trying to have a completed project by the end of the week,
to recap thank you for your help. I am redoing the database based on some of your advice.
I have created the tables as above
and i am working in a saved copy to try my own ideas and a good copy to save what is working.

 

[m17347047711116]

Getting there

O.K I am getting there RuralGuy, you have been key

Tables complete, form interface complete
Next challenge is that now that i see the Parent Form and Sub Form the way i want when i update the Sub Form with [Score values] they are entered into their correct table but the other supporting information is not updated therefore the [score values] are not associated to a .contestant a . category or a .judge those specific values are not updated in the score table.

the query i have created is working in the sense that is queries the results together correctly but the relationships are not quite right.
I will try to describe the query:

[Table Event Judges]
.Judge Name
.Event
.Category
.Position
.Remarks

[Table Entry Info]
.Event
.Category
.Class
.Contestant Name
.Remarks

[Table Scores]
.Event
.Category
.Contestants Name
.Contestants Name
.Judges Name
.Symetry Score1
.Round Score2
.Remarks

Green = one to one relationship between tables
Blue = One to many relationship between tables

Red = the fields in my scores database that still have to updated manually for the entry to take effect in the scores table.
I would expect to enter the Score1 and score2 but the other fields are duplicate information that is already in the other tables i should not have to enter it again to create the data.
I may be over complicating things in the table set up ?