[AccessD] Question of process

Just off the top of my head:

PE_ID Autonumber (of course) ;-)
PE_LName text	- Person Last Name
PE_FName text	- Person First Name

CT_ID	Autonumber 	- contact type ID
CT_Type Text	- Type of contact

CA_ID	Autonumber	- Case ID
CA_Number text	- Case Number
CA_OtherStuff	- Other fields

PECA_ID Autonumber	- Person Case ID
PECA_IDPE long	- Person ID
PECA_IDCA long	- Case ID
PECA_IDCT long	- Contact Type

The first two tables are your two "main" tables, people and cases.
The third is a contact type (Attorney, defendant etc)
The fourth is a many-many which relates people and cases with a contact
type.

Something of that nature anyway.

John W. Colby
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Clark
Sent: Wednesday, January 28, 2004 10:39 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Question of process


I am beginning a new project. The purpose of this program will be to
track indictment cases for the District Attorney's office. My question
isn't so much technical, like usual, but rather 'technique'.

I'll start with a scenario:

Lets say I am a Asst. DA, so I am in the system as such. Then after a
couple of years, I go into private practice and become a defense
attorney; I may now be in the system as the lawyer for a defendant. Now
let's assume that I am assaulted and my attacker is charged, so now I am
in this system as a victim of this case. Finally, I cross over to the
dark side (I know lawyers are already there), and I am indicted for a
crime of my own. I will now be in the system as a defendant, a victim, a
defense attorney, and an ADA...these last two, very probably, several
times.

My situation is that I would like to bring up every instance of a
person fitting this description, or similar ones. For Instance a report
of the previous example might produce the following:

JOHN W CLARK

#2001-023    ADA
#2001-102    ADA
#2001-140    ADA
#2002-334    ATTORNEY
#2003-153    ATTORNEY
#2003-543    VICTIM
#2003-654    ATTORNEY
#2004-030    DEFENDANT

I had originally thought to create a table for the 'types' (i.e.
attorney, ADA, victim, etc.), a table with names and personal info, an
indictment table, and link these all via a fourth table, but this soon
seemed insufficient. The average indictment number will have at least
five people connected to it (Defendant, Attorney, ADA, Victim, and
Judge), and possibly many more, with multiple victims and/or
co-defendants.

Am I over thinking this? Would it just be as simple as having a table
for each type and searching these tables by indictment number later in
the program?

I thought that maybe, by typing this out, it would be clearer in my
mind, by the time I was done, but I am still fuzzy with it!

Thanks for any insight or advise you could give me!

John W Clark
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com