First of all, let's clarify about relationships. Relationships don't
"do" anything. They don't create records, or manipulate data.
I was using the word in the informal sense anyway. In other words, are
two data entities related to each other. For example, you told me that
there can be more than one Party for any given Case. Ok, that means
that Case and Party are related, one-to-many. In real life. If you
define a Relationship formally, within the database, is not really the
point. If you do, that's fine, but it won't do whay you apparently
think it will do. If you enforce Referential Integrity in the
Relationship definition, then that will prevent a Party record being
entered for a CaseID that does not exist. But that's about the limit of
how much use Realtionships are.
Ok, now, do I understand you correctly?... The Evaluations are for each
Party in each Case?
In that case, then the appropriate way to structure this is by putting a
PartyID field in the Evaluations table, and *not* a CaseID. If you know
the PartyID, then the associated CaseID is automatically known.
1 First Case
2 Second Case
PartyID CaseID NameOfParty
1 1 Fred
2 1 Betty
3 2 Wilma
4 2 Barney
Ok, this means that Fred and Betty are Parties in the First Case, and
Wilma and Barney are Parties in the Second Case. Does that make sense?
Ok, so now we have...
EvaluationID PartyID Evaluation
1 2 Not bad
2 3 Cool
So the first Evaluation relates to PartyID=2 which is Betty. Betty is a
Party in CaseID=1. We know that from the Party table. It is absolutely
wrong to store it again in the Evaluations table.
YHowever, there is something else besides, if you can follow me so far.
You mentioned one-to-one. Do you mean that each Party will only ever
have one Evaluation, or is it possible for there to be more than one
Evaluation for any given Party? If only one, then you probable
shouldn't even have a separate Evaluations table at all - the Evaluation
data should be in the Party table.
Steve Schapel, Microsoft Access MVP