automatically create a new record in a table one table away

automatically create a new record in a table one table away

Post by Steve Scha » Sat, 06 Jan 2007 06:01:42


Janis,

To me, this implies that each Case can have more than one Party. Is
that correct?

--
Steve Schapel, Microsoft Access MVP
 
 
 

automatically create a new record in a table one table away

Post by Steve Scha » Sat, 06 Jan 2007 06:44:24

Janis,

Thanks for the further clarification. I was not party to your
discussion yesterday.

However, it seems there is something awry with your table design. You
have referred to CaseID and PartyID fields, and I will assume these are
the primary key fields of the Cases and Party tables. Is that correct?

And then, you want a related record in the Evaluations table. Well, it
seems to me that the relationship is probably via the PartyID. I expect
you do not need or want a CaseID field in the Evaluations table. If you
know the PartyID, and each Party record is associated already with a
Cases record, then... if you know the PartyID then you automatically
know the CasesID.

So, unless I am missing something in your meaning, there is a
one-to-many relationship between Party and Evaluations, and would be
based on field in common being PartyID.

--
Steve Schapel, Microsoft Access MVP

 
 
 

automatically create a new record in a table one table away

Post by Steve Scha » Sat, 06 Jan 2007 08:07:14

Janis,

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.

Table: Cases
CaseID NameOfCase
1 First Case
2 Second Case

Table: Party
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...

Table: Evaluations
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
 
 
 

automatically create a new record in a table one table away

Post by John Vinso » Sat, 06 Jan 2007 10:05:38

On Thu, 4 Jan 2007 13:28:00 -0800, FilemakerPro_Developer



You still seem to be assuming a) that a relationship will create a new
record, and b) that it is necessary to create a new record before you
can enter any data into that record.

BOTH ASSUMPTIONS ARE WRONG.

You *NEVER* need to programmatically create a blank record prior to
entering data.

Access will *NEVER* create a blank record for you based on entering a
record into some other table.

The way to create a new record is to... create a new record, by
starting to enter data into it. Use a Subform; you'll see a blank
record on the form (which is *JUST* a data entry tool, there is no
blank record in the table); when you start typing into it, a record is
created right then, when you start entering data on the form.

John W. Vinson[MVP]