Query Newbie

[m17347047711116]

I have been reading this is how I am able to articulate my problem with some degree of knowledge.

I have 3 tables as Follows:

Table 1. [ID]pk, [Contestant Name],[Event],[Category],[Class]

Table 2. [ID]pk, [Judge Name],[Event],[Category]

Table 3. [ID]pk, [Contestant Name],[Event],[Category],[Class],[Score1],[Score2],[Remarks]

Table 3 is a record of score from a specific Judge to a specific contestant. There will be a record for each contestant from each judge that is judging that specific [Event] & [Category]. therefore if there are two judges and two contestants there will 4 records entered into the table.

Problems 1. (I can't get my tables related properly)
I should not have to enter the duplicate information each time I enter a record into the Table3. The common information should be available within the other tables using queries.

Why i think I have problem 1:
There is a many to many relationship here on the parent form what i want is the [Judge Name], [Event], [Category].
On the related Sub form i want to see a query that shows me parts of tables 1,2,3 for each Contestant that is entered in the matching [Event],[Category] combined with the query of 1,2,3 and be able to enter this judges scores against the contestant Name and then when I change to a new judge I may see the same contestants but be able to enter a new score record against that contestant with a different judge.

In English
Assume you are a judge for a contest you have a form in front of you
Parent form Select your Event, Name, Category from a pull down box in the sub form you are presented with a list of Contestants that are on stage. You enter Score1 & Score2 for each Contestant and the record is created.

Judge #2 is doing the same thing
He selects his Event,Name, Category from the pull down menu on the Parent form and walla the subform populates with the same contestants as Judge#1 and he enters his Score1 & Score2 into the subform
and
What we have is 2 Judges, 5 Contestants and 10 Records created into Table3

Sorry for the length but the last part helps me visualize what I am trying to explain.

End result is I can't get it to work is this a job for a union query ?

 

[neileg]

A few thoughts.

Since Table 3 contains all the fields from Table 1, Table 1 is redundant, so that can't be right.

Assuming that a contestant can enter more than one event and category, you have a many to many relationship between contestants and event/categories. That implies a junction table. Your scores should be held in another table that links to the junction table. You havent set out the rules for scoring, but you seem to have repeating groups for scores. These would normally be individual records not individual fields.

I'ts not clear if there is more than one judge for a category. If there is, then there is another many to many and another junction table.

Union queries have nothing to do with what you want.

 

[CEH]

Have something that might help....

Since table 3 contains BOTH table one and table two, both are redundant. If I am reading your post correctly what it ends up being is a scoring system? I have something that might help... It is a rating system for inspections. I have Jobs, and inspections instead of Contestants and judges, and "Areas", "Task" and "Rating" instead of "event, catagory, and score" but it sounds close to the same setup.... Table for Judges, table for Contestants, so on.... In mine you pick a Job, it asigns an Inspection Number, then you pick and Area,(from drop down) Task, (from another dropdown), and a rating... 1 to 5....... The DB is too big to post here, but if you would like to look at the structure let me know and I'll get it to you. I think it might help.