Junction tables and data entry

[Cheese_whiz]

I was told some time back that my project needed a couple of junction tables.
Specifically, I'm working on a legal case database, and I need to collect
data about the parties involved in every case. The primary tables for the
project, tblCase and tblParty, have a "many-to-many" relationship, hence the
need for the junction tables.

What I don't know is how the junction tables get populated? Can you send
data from a form to two diffferent tables at once? Also, assumming I CAN
populate two tables with one form (including sending the same individual
piece of info to two different tables), could a choice made by the user using
a combo box or option group (options: defendant or plaintiff (and possibly
attorney and judge)) dictate WHICH junction table ALSO received the data from
the form ("also" because the "tblParty" would need most of the information).

I really could use some clarification on this thing. If I need to be
clearer, I'll give it a try.

Thanks,
CW

 

[Cheese_whiz]

The junction tables, if it matters, would be tblCaseDef and tblCasePlaintiff
(along with some other ones for judges and lawyers), but these two cover
defendants and plaintiffs which is good enough for this post.

 

[KARL DEWEY]

Can you send data from a form to two diffferent tables at once?
In using junction tables the information exist in the ONE table, you are
just adding from both ONE table into two fields of the MANY (junction) table.

tblCase and tblParty are your ONE tables. The junction table identifies the
parties involved in a case. It also identifies the cases a party is involved.

Use a form and subform. You will need two main forms for the junction - one
for tblCase and one for tblParty.

tblCase in main form and tblParty in the subform you move the main to the
case and in the subform select the party from a listbox or combobox. You can
add new cases in the main form but normally will need to add new parties from
the tblParty main form. If you use the Not In List action you can add on the
fly.

The tblParty main form will work the same.

 

[Cheese_whiz]

Thanks for the reply,

Are you suggesting two forms for entering parties into a case, one as the
main addParty, and another as a subform on the main addCase form?

I still haven't figured out what these junction tables should look like when
they are done. Do they have any data or are they just created to use in the
"relationships" thing? I know the data is already going to be in the main
tables, but I don't know if I have to get it into the junction tables or not.
It's a basic question, but it's my first junction table so....

I' did understand what you were saying for the most part, it's just a couple
of the key details are still a little confusing.

Thanks again,
CW

 

[KARL DEWEY]

Are you suggesting two forms for entering parties into a case, one as the
main addParty, and another as a subform on the main addCase form?
Yes but the case must normally is created from a main form as there are more
details to a case than you would display as part of the party form/subform.
The one-to-many relationship is set using the primary key of the ONE table
and a matching datatype as a foreign key in the MANY table.
Most of people use an Autonumber field for the ONE table and then a Number -
Integer for the foreign key of the MANY table.

The junction table would have as a minimum tblCaseID and tblPartyID. You
can also have fields to indicate when it was propose, effective, terminated,
entered by, terminated by, and a boolean (Yes/No) field for archived.

These added field would be a part of your subform.
In the subform of the case main form select the party from a listbox or
combobox. The listbox or combobox will use the tblCase or tblParty as record
source.