Legal Database

[Cheese_whiz]

I'm trying to setup a legal database that will essentially be a repository
for summarized info about court cases. One of the early problems I'm having
is with how to treat multiple defendants. Often, there may be three or even
more defendents listed in a case.

It seems to me that in SIMILAR circumstances, I've seen it suggested that a
seperate table is appropriate. However, this situation is different than
what i've seen before because there is essentially an infinite number of
defendants in my files whereas in the similar situations there seems to
largely have been only around 5-10 potential "options" that could have been
included.

Should I just "guess" at the maximum number of defendants I'll ever see and
include all those fields (10+ per defendant X number of defendants) in my
main table even though usually there will be only one defendant? Is there a
way to add a new set of fields (or, rather than "add" maybe the proper term
is "make available" or "make visible") for every set that you fill out, such
that when you input the data for defendant one, you'll automatically get
room for "defendant 2" and so on? Should defendants be in a seperate table
even though there's potentially an infinite number of them.....while one or
two things like agencies might be defendants in multiple cases, individual
defendants will frequently only appear in one case ever.

Any help would be appreciated.
Thanks in advance.

Cheese_"notthatmuchofa"_whiz

 

[Craig Alexander Morrison]

For the sake of simplicity let's say you have just one table called
CourtCase (each record this contains data about each court case).

If you have any fields that may have to be repeated such as Defendant then
create a Defendant table (each record will contain information about each
defendant).

The next stage is that you need to link these Defendant(s) to the CourtCase
this is done by creating a third table which we will call the
CourtCaseDefendant (each record will relate one Court Case to one defendant)
all this table may need is the Primary Key from the CourtCase table and the
Defendant table which are then made into the Primary Key for the
CourtCaseDefendant table.

Let's say the CourtCase table has a Primary Key of CaseNumber and the
Defendant table has a Primary Key of DefendantReference then the Primary Key
of the CourtCaseDefendant table will be both these Primary Keys (which for
simplicity we shall assume they are single fields) the Primary Key on our
link table will be made up of both of the fields required to define each
Primary Key.

This goes for all other information such as ChargesMade etc etc.

The power of this approach allows one to easily see if a Defendant is
involved in more than one Court Case etc. etc. And of course it allows you
to have as many defendants as you need or indeed charges or plaintiffs or
etc etc.

The basic rule is that each table should contain all the information
pertaining to one thing and related things should be in related tables. If
you find you need to have information in fields that seem to be repeated
then they are different things about which you wish to know, this is part of
the normalisation process that you may need to learn a bit about to allow
you to complete your project successfully.


--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

 

[Larry Daugherty]

I'll assume that over time people could be defendants in many cases
and she same person could also be plaintiff in many cases. To handle
those situations most efficiently you'd want four tables. One table
would be tblCase. The other high level table would be tblPerson. The
remaining two tables would be junction tables: tblCaseDefendant and
tblCasePlaintiff. Those junction tables are what make it possible to
show "many-to-many" relationships. There will be an entry in
tblCaseDefendant for each defendant in a particular Case. Likewise an
entry for each plaintiff in a particular case. Junction tables hold
the foreign keys of each of the higher level tables and, usually, any
additional attributes and notes about this instance of the
intersection. By doing it as suggested above you could base a form on
tblCase and have subforms on tblDefendant and tblPlaintiff.

Also, tblPerson could hold information about any person of interest to
your application, not just litigants.

HTH

 

[Cheese_whiz]

Craig,

Thanks a lot for the thorough reply. That's most helpful.

I am somewhat familiar with the normalization "rules", I just don't have
much in the way of experience in implementing them, so unless my problem is
nearly identical to the examples I've seen, I get concerned about whether or
not I'm doing the appropriate thing.

I'm going to have to think a bit about this and try to get the tables set up
and then maybe I'll have a followup for you if you are around later. I'm
assumming I was clear about the fact that many of the defendants (indeed,
most of them) will only be party to one case. I'm also assumming that I
remember correctly that it's possible to have one form send data to more than
one table. I'd hate to have to set it up so that whoever inputs the info has
to open a seperate form just to type in defendant names...

Anyway, thanks a lot for the response!

CW

 

[Cheese_whiz]

Well, for some reason my reply never posted....

Thanks for the info, Larry.

I think I follow what you are saying, except I'm not sure I have a handle on
how the tblPerson gets populated. Would it be the case that every time you
had a new case that had one or more parties that were not already in the
database, that you'd enter their info in the tblPerson table and then the
junction table entries would just refer to the corresponding entries in that
tblPerson table?

Thanks again.
CW

 

[Larry Daugherty]

You've got it perfectly. You enter people into tblPerson as soon as
they're in view of your application. You could use a combobox to find
the people involved.
Using autoexpand you just start typing the person's last name. Use
the NotInList event to open a second form to gather all of the info
for a new entry. That will work just fine until you have several
thousand records in tblPerson. You may have to devise a speedier
search method when the records get into the hundreds of thousands.

There may be example code for the NotInList event on
www.msps.org/access or in one of the books you're using. If not, post
back for the code.

HTH

 

[Larry Daugherty]

Just caught myself in a lapse into carelessness. In those cases where
I say you enter something into a table I mean implicitly that you'll
use a form to do so. Don't enter data directly into tables.

HTH

 

[Cheese_whiz]

Thanks again, Larry,

Your responses, along with Craig's initial replay, have been extremely
helpful. I have a MUCH better idea of how things need to come together now.
I'm not sure about the combo box option at this point, since shortly after
"launch" this database should have at least a few hundred records (and easily
could reach several thousand with a few days), but I don't know much about
combo boxes so I'll just have to cross that bridge after I do a little
research.

I also tried to pull up that link you gave for the NotInList Event you
suggested, but the page doesn't load and if I try to get to the front of the
domain it appears that maybe it's not the one you intended.

Anyway, thanks again. MOST helpful.
CW

 

[Larry Daugherty]

Sorry, I don't see well and don't check my typing closely enough.

WWW.MVPS.ORG/ACCESS

About the combobox and speed; hundreds or thousands of records, no
problem. Hundreds of thousands of records might become a problem.
Your mileage may vary.

HTH