DESIGN DATABESE/PROBLEM WITH SUBFORMS

PLease help me with designing the tables and setting the relationships as i
am unable to work with subforms.
The databse has to be designed for interschool competions where various
types of comtetions are held. Each competion is judged by more than 3 judges.
Participants can participate in more than one competions. the average marks
by all the judges is the marks for that participant. ALSO ONE JUDGE CAN BE A
JUDGE FOR MANY COMPETIONS AND A STUDENT CAN PARTICIPATE IN MORE THAN ONE
COMPETIONS.
So the finally participantCode, JudCode, Competitioncode together has to be
unique for a marks entry. Also while entering the data competionwise
participants data is easier to add. considering all these constraints how i
create other tables than

PArticipant table
partCode
partName
.....

Competition Table
compCode
competion
no. of judges
....

Would need to know/ check a lot more details, but here's my guess at a
structure that would provide a good foundation for you.

Under each table I listed a fe key fields, add fields for the other
information for that entity

Table of Participants

Field: ParticipantID (PK)


Table of Competitions (genera, not individual participations)

Field: CompetitionID (PK)


Table of Judges

Field: JudgeID (PK)


Table of instances of a person competing in a competition

Field: CompInstanceID (PK)
Field: ParticipantID (FK)
Field: CompetitionID (FK)


Table if instances of a Judge judging an instance of a person competing in
a competition

CompInstanceID (FK)
JudgeID (FK)
Score


Then link all fields to their namesakes (PK to FK)

Thanks fred for taking interest in my design.
What more details u want?

"Fred" wrote:

Would need to know/ check a lot more details, but here's my guess at a
structure that would provide a good foundation for you.

Under each table I listed a fe key fields, add fields for the other
information for that entity

Table of Participants

Field: ParticipantID (PK)


Table of Competitions (genera, not individual participations)

Field: CompetitionID (PK)


Table of Judges

Field: JudgeID (PK)


Table of instances of a person competing in a competition

Field: CompInstanceID (PK)
Field: ParticipantID (FK)
Field: CompetitionID (FK)


Table if instances of a Judge judging an instance of a person competing in
a competition

CompInstanceID (FK)
JudgeID (FK)
Score


Then link all fields to their namesakes (PK to FK)

The structure that I recommended is pretty powerful / general cases, and so
it would most likely work. Any questions would to tell us more about the
nature of your competitions and data, and then we'd be looking at that to see
if there is anything important that you didn't tell us about originally.

Probably the biggest question mark is what PRECISELY do you mean by "a
competition". Without knowing about the nature of your competitions, I
don't even have the nouns to ask the question. I already ruled out calling
one person's participation a "competition". But I'd still be looking for
the possibility that your word "competition" might mean two different things.
For example, For example if one competition is Youth 100 Yard Rifle
Range, and 100 youth will be participating, and you split it into a 50 person
2:00 session and a 50 person 4:00 session, would you call

1. "Youth 100 Rifle Range" a "competition"
2. "2:00 Youth 100 Yard Rifle Range" a "competition"
3. Both #1 and #2 a "competition". In this case your naming is ambiguous
and you'll have to start by fixing that problem.


One more recommendation: don't confuse your important effort to get the
structure right by throwing in the word "subforms". Those come later.

Hope that helps a little.

HI!
Thanks again!
Sorry, i put the question under subform title as i had already created
tables and got stuck when i WAS creating form to select judgenames for a
particular competitoon.
So i thought my structure of tables might be wrong.Sorry!!!


These are interschool competitions which are individual(student) orcan be
group (a school) also . e.g. group dance, debate(individual), flower
decoration,drawing, addvertising a product(group). I will enter school name
as a single entry(participant) for group competitions and student names for
infdividual competitions.

Now i have created the tables as per yr suggesstion.i have created a subform
which diplays/can add corrospoing judges for a particulat competition( which
is in main form).
When i open the form it displays first competition in the main form and
corrospoing judges for that comp . i am able to add new judges for that
(first record of main form) competition. The problem arises when i click on
next record for main form or select one from the combobox for competiton. it
says error : can't be edited as it bound to compcode.
what mistake am i doing?
tables are as follows

competition table
compcode (PK)
compname
.....


compJudge table
compcode(MPK)
judcode(MPK)

judge table
judcode(PK)
judge
......


link master field :compcode
link child field: compcode

There are many things adding together here to the point that it would take a
lot of posts just to figure out where you're at much less what to do.

You have to start with a table structure. The partial glimpse that you gave
in the last post was enough to see that you did not use the one I
recommended, but not enough to saa what you's actually is.

Your last post tends to reinforce my guess is that your main problem is that
you keep trying to build the third floor before you built the foundation.
I.E. you are not being sufficiently thorough regarding table structure and
are jumping right to forms.

If you want to force yourself onto a successful track on the, would suggest
starting by dealing only with the nature of the process that you want to
database and table structure. And if you havea troubles in that area,
repost, as a strictly table structure question.

Hope this helps, even if it not what you expected.