Access 03 query question

[Robert]

I am an attorney in a non-profit organization and a self-taught programmer.
I'm trying to create a client db that will allow me to search for potential
conflicts of interest based either on Social Security # or on Last Name.
I've created two different tables with the following fields in each table:

ClientInfo
Client# (primary key)
First Name
Middle Name
Last Name
SS#
(other contact info)

OpposingInfo
____________ (primary key)
First Name
Middle Name
Last Name
SS#
(other info)

I have a couple questions.
(1) Should the Primary key on OpposingInfo be Client# and linked to the
same field in ClientInfo? Or would it be better to have a different primary
key and then create a field name Client# that is related to the Client# in
ClientInfo? I'm guessing the latter

(2) Is it possible to create a query that will allow me to enter a SS#
and it will search both tables for matching info? If a match is found in
either table, I would like the results to show the Client's Names and SS#
and the Opposing Party's Names and SS#.

I've been searching through news group postings and am not getting a good
answer to this. I am certain it can be done, just having trouble making the
connection in my mind that will make the connection in the computer. I'm
using Access 2003.

Thanks in advance.
Robert

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Jeff Smith]

"Robert" <sanfordlaw@*** inebraska.com> wrote in message
news:11******** ******@sp6iad.s uperfeed.net...

I am an attorney in a non-profit organization and a self-taught programmer.
I'm trying to create a client db that will allow me to search for potential
conflicts of interest based either on Social Security # or on Last Name.
I've created two different tables with the following fields in each table:

ClientInfo
Client# (primary key)
First Name
Middle Name
Last Name
SS#
(other contact info)

OpposingInfo
____________ (primary key)
First Name
Middle Name
Last Name
SS#
(other info)

I have a couple questions.
(1) Should the Primary key on OpposingInfo be Client# and linked to
the same field in ClientInfo? Or would it be better to have a different
primary key and then create a field name Client# that is related to the
Client# in ClientInfo? I'm guessing the latter

(2) Is it possible to create a query that will allow me to enter a SS#
and it will search both tables for matching info? If a match is found in
either table, I would like the results to show the Client's Names and SS#
and the Opposing Party's Names and SS#.

I've been searching through news group postings and am not getting a good
answer to this. I am certain it can be done, just having trouble making
the connection in my mind that will make the connection in the computer.
I'm using Access 2003.

What is the purpose of the OpposingInfo table? Is it going to be linked to
the ClientInfo table? If yes, what is its relationship to the ClientInfo
table? One client has many opposing information? One client has only one
opposing information?

If the two tables or linked there is no need to repeat the fields in the two
tables. Reply back with more information about what you're trying to do and
we'll guide you on the correct path.

Jeff

[Robert]

The purpose of the OpposingInfo table is to store information about an
opposing party, such as contact info, social security number, opposing
attorney and things like that. If I am representing A in a case against B,
I need to make certain that no one in our office later accepts B's case. My
thought is it will be easier to work with the data if I have both an
OpposingInfo table and a ClientInfo table.

Yes, the two tables need to be related. I need to be able to search and
determine any type of connection B has to any of my past or current clients.
B could relate to many clients and A could relate to many opposing parties
if A has several different suits going on (suit again husband for divorce,
suit against landlord, etc.).

Hope this extra info helps.

Robert

What is the purpose of the OpposingInfo table? Is it going to be linked to
the ClientInfo table? If yes, what is its relationship to the ClientInfo
table? One client has many opposing information? One client has only one
opposing information?

If the two tables or linked there is no need to repeat the fields in the
two tables. Reply back with more information about what you're trying to
do and we'll guide you on the correct path.

Jeff

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[david epsom dot com dot au]

In general, it is better to put information like

First Name
Middle Name
Last Name
SS#
Other Contact Info

all in one table.

Then you either have a 'client' checkbox (boolean)
to separate out the clients from the counter-parties,
or you have a link to the Client table with the
client number and other client information.
If you start out very simply, you can have just one table
with all the information in it.

You need more tables when a person can be linked to
multiple cases, or multiple addresses, or multiple
bank accounts, or multiple anything.

But even if you have multiple link tables, you normally
have one Person table for Persons.

And that makes it easy to search for a Person or SSN
to see if there is a conflict.

(david)


"Robert" <sanfordlaw@*** inebraska.com> wrote in message
news:11******** ******@sp6iad.s uperfeed.net...

I am an attorney in a non-profit organization and a self-taught programmer.
I'm trying to create a client db that will allow me to search for potential
conflicts of interest based either on Social Security # or on Last Name.
I've created two different tables with the following fields in each table:

ClientInfo
Client# (primary key)
First Name
Middle Name
Last Name
SS#
(other contact info)

OpposingInfo
____________ (primary key)
First Name
Middle Name
Last Name
SS#
(other info)

I have a couple questions.
(1) Should the Primary key on OpposingInfo be Client# and linked to
the same field in ClientInfo? Or would it be better to have a different
primary key and then create a field name Client# that is related to the
Client# in ClientInfo? I'm guessing the latter

(2) Is it possible to create a query that will allow me to enter a SS#
and it will search both tables for matching info? If a match is found in
either table, I would like the results to show the Client's Names and SS#
and the Opposing Party's Names and SS#.

I've been searching through news group postings and am not getting a good
answer to this. I am certain it can be done, just having trouble making
the connection in my mind that will make the connection in the computer.
I'm using Access 2003.

Thanks in advance.
Robert
----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----

[Keith Wilby]

"Robert" <sanfordlaw@*** inebraska.com> wrote in message
news:11******** ******@sp6iad.s uperfeed.net...

I am an attorney in a non-profit organization and a self-taught programmer.
I'm trying to create a client db that will allow me to search for potential
conflicts of interest based either on Social Security # or on Last Name.
I've created two different tables with the following fields in each table:

ClientInfo
Client# (primary key)
First Name
Middle Name
Last Name
SS#
(other contact info)

OpposingInfo
____________ (primary key)
First Name
Middle Name
Last Name
SS#
(other info)

Hi Robert,

There's no need to have two tables for this info - have one client table and
a flag to denote whether or not it's your client. Your queries should do
the rest.

Regards,
Keith.
www.keithwilby.com

[Jeff Smith]

"Robert" <sanfordlaw@*** inebraska.com> wrote in message
news:11******** ******@sp6iad.s uperfeed.net...

The purpose of the OpposingInfo table is to store information about an
opposing party, such as contact info, social security number, opposing
attorney and things like that. If I am representing A in a case against
B, I need to make certain that no one in our office later accepts B's
case. My thought is it will be easier to work with the data if I have
both an OpposingInfo table and a ClientInfo table.

Yes, the two tables need to be related. I need to be able to search and
determine any type of connection B has to any of my past or current
clients. B could relate to many clients and A could relate to many
opposing parties if A has several different suits going on (suit again
husband for divorce, suit against landlord, etc.).

Hope this extra info helps.

David has given you some information to think about.

What you'll have to think about is, "Can there be more than one opposing
party per case?" The answer to this question will determine the design of
the database.

If the answer is Yes then I'd do a table structure like this:

tblPerson
PersonID (Primary Key, Autonumber)
FirstName
LastName
SS#
Other Info

tblCase
CaseNo (Primary Key)
PersonID (Foreign Key linked from tblPerson)
CaseDate
CaseDetails
Completed (Yes/No)
Other info

tblOpposing
CaseNo (Foreign Key linked from tblCase)
PersonID (Foreign Key linked from tblPerson)
AttorneyName

The two foreign keys in tblOpposing combined makes the Primary Key. All
information about every person involved in any case is stored in the
tblPerson table. You don't need a field in that table to mark them as the
opposition, what if one day you may end up representing them in another
case. The tblCase table stores details about the case (you may need another
linked table if you want to record multiple details of similar nature) and
also stores the ID of the person you're representing.

The tblOpposing table is the many side of the tblCase, a case may have more
than one person on the opposing side where there's a separate field to store
the Attorney's name. You could create another table to store details about
every Attorney and place the Attorney here instead.

Hope this helps
Jeff

[rkc]

Robert wrote:

I am an attorney in a non-profit organization and a self-taught programmer.
I'm trying to create a client db that will allow me to search for potential
conflicts of interest based either on Social Security # or on Last Name.
I've created two different tables with the following fields in each table:

Clients are people regardless of their choice of lawyer.
They all belong in the same table. Litigants maybe?
Then a Case table would allow you to relate a Litigant
to a case via a Case/Litigant table. There would be
multiple rows in the CaseLitigant table. One for each
participant with a field indicating if they are your
client on that case.
Case
CaseID
(Case Info)

Litigants
LitigantID (primary key)
First Name
Middle Name
Last Name
SS#
(other contact info)

CaseLitigant
CaseID
LitigantID
Client (yes/no)

[cjb_kjb]

It depends how complicated you want to get with this. The problem you
are trying to solve could become very complex indeed - it just depends
on what degree of complexity you want to go to. Your original solution
is very simple and would probably suffice.

It appears to me you may be trying to prevent more than the simple
matter of - If we represent client A in A v B we want a system to
prevent us from representing B as well. Do you want this system to
prevent you from representing B's son, brother, business partner. If X
arrested A and you are defending A do you want the system to prevent
you from representing X in another case. If you represented B two
years ago - is it a conflict if you now represent A against B?
Similarly if you represented B's son previously is there a conflict by
representing A. So this can become very complicated with Cases,
Litigants, Associates etc. Of course there are degrees of conflict
according to the remoteness of an Associate to any party to a case and
the time elapsed since somebody was represented.

Your original model may not take care of all these problems if infact
it needs to.

Going back to your original model:

I think Client table is more likely ClientCase. If this is so then it
makes sense to have the key of this table in the OpposingInfo table
because you then know who is opposing your client in this case.

There is a strong case as suggested above to have a person table, but
this brings in other issues - like more complex joins and what do you
do when you are adding a person who is already in the Person Table.

Identifying people is always a problem too. If you always have a SS# -
there is no problem because you have a unique identifier, if not and
you have to match on names sometimes there are issues. You'll never
match if there are spelling mistakes (unless you match using a soundex
algorithm - still no certainty), Is the John Smith you are adding the
same John Smith already in the database (even if same address - could
be father and son), is Colin Jones the Collin Jones you already have
and so it goes on.

If you don't have this skills to resolve these issues or dont care
about them then keep to your original model.

to find matching people in each table on SS# -
Select "Client", ss#, FirstName, MiddleName, LastName from ClientInfo
UNION Select "Opposing", ss#, FirstName, MiddleName, LastName from
OpposingInfo where SS# = form!aaa!SS#

In a Union query you must select the same number of fields from each
table. If you want a field from one table that is not in the other use
"" (if it's character or maybe 0 if numeric) in place of the field in
the other Select clause. If for example Opposing table had a field
AssociationType (to identify if the person is the Opponent, related to
the opponent, advocate for the opponent etc but this field isnt in
ClientInfo the above query would become:
Select "Client", ss#, FirstName, MiddleName, LastName, "" as
AssociationType from ClientInfo UNION Select "Opposing", ss#,
FirstName, MiddleName, LastName, AssociationType from OpposingInfo

Save this query as say Query1

Then write another query
Select * from Query1 where SS# = SS or if you want it to use a value
from a form then
Select * from Query1 where SS# = forms!formname! fieldname

You could have the where clause twice in the original query and not
have Query2 but it's simpler if you have the 2.

Also best to have an index on the fields in the tables you are matching
on - index on SS# for sure, also on Name if you want a query that
searches by name instead of SS#

[Robert]

Jeff, thanks. I'm going to have to look at your suggestions a bit more
closely. You've touched on a couple things that are key.

1. I would like to develop this so that there can be multiple parties
involved. For instance if it is an abuse/neglect case an attorney could not
represent the children and any one parent, or both parents for that matter,
at the same time. In that case, there would be multiple "opposing parties"
as there could be in many types of cases.

2. You also raise the question of whether it would be possible to represent
someone that was formerly an opponent. In the right situation, that would
be a possibility and something I want to consider.

As mentioned above, I work for a non-profit. I send cases to attorneys in
law firms to represent a client. Many of the attorneys taking cases through
our program are solo practitioners and have very archaic methods for time
keeping/conflict checking. While I do a lot of this in our office to make
certain I don't place both parties in a case, I'd like to offer something to
contracting attorneys to entice them to take more of our cases. So for me,
it could be a simple db checking to see if we have opposed an individual
applying for services. But in the broader sense, I'd like to something
bigger taking into account things you and others have noted.

Robert
"Jeff Smith" <No****@Not.Thi s.Address> wrote in message
news:e5******** **@lust.ihug.co .nz...

"Robert" <sanfordlaw@*** inebraska.com> wrote in message
news:11******** ******@sp6iad.s uperfeed.net...

The purpose of the OpposingInfo table is to store information about an
opposing party, such as contact info, social security number, opposing
attorney and things like that. If I am representing A in a case against
B, I need to make certain that no one in our office later accepts B's
case. My thought is it will be easier to work with the data if I have
both an OpposingInfo table and a ClientInfo table.

Yes, the two tables need to be related. I need to be able to search and
determine any type of connection B has to any of my past or current
clients. B could relate to many clients and A could relate to many
opposing parties if A has several different suits going on (suit again
husband for divorce, suit against landlord, etc.).

Hope this extra info helps.

David has given you some information to think about.

What you'll have to think about is, "Can there be more than one opposing
party per case?" The answer to this question will determine the design of
the database.

If the answer is Yes then I'd do a table structure like this:

tblPerson
PersonID (Primary Key, Autonumber)
FirstName
LastName
SS#
Other Info

tblCase
CaseNo (Primary Key)
PersonID (Foreign Key linked from tblPerson)
CaseDate
CaseDetails
Completed (Yes/No)
Other info

tblOpposing
CaseNo (Foreign Key linked from tblCase)
PersonID (Foreign Key linked from tblPerson)
AttorneyName

The two foreign keys in tblOpposing combined makes the Primary Key. All
information about every person involved in any case is stored in the
tblPerson table. You don't need a field in that table to mark them as the
opposition, what if one day you may end up representing them in another
case. The tblCase table stores details about the case (you may need
another linked table if you want to record multiple details of similar
nature) and also stores the ID of the person you're representing.

The tblOpposing table is the many side of the tblCase, a case may have
more than one person on the opposing side where there's a separate field
to store the Attorney's name. You could create another table to store
details about every Attorney and place the Attorney here instead.

Hope this helps
Jeff

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Robert]

So much for a simple project. lol. Everyone has given some great
suggestions and things to think about. I really appreciate the input.

I think I like your suggestion about ClientCase. This brings up another
question. If "A" comes in for a divorce from "B" and "C" is suing both "A"
for a violation of a lease agreement, does your suggestion of having a
"ClientCase " table allow me to enter both? If I'm following your suggestion
I think it would. Then the other question with regard to this is if this
goes beyond just a client/conflict db and I add into it a
timekeeping/billing segment, would using your ClientCase suggestion allow me
to send out a bill for both cases in one statement?

I should have stopped when this was just a thought in the ol' noodle.
"cjb_kjb" <cj*****@hotmai l.com> wrote in message
news:11******** **************@ y43g2000cwc.goo glegroups.com.. .

It depends how complicated you want to get with this. The problem you
are trying to solve could become very complex indeed - it just depends
on what degree of complexity you want to go to. Your original solution
is very simple and would probably suffice.

It appears to me you may be trying to prevent more than the simple
matter of - If we represent client A in A v B we want a system to
prevent us from representing B as well. Do you want this system to
prevent you from representing B's son, brother, business partner. If X
arrested A and you are defending A do you want the system to prevent
you from representing X in another case. If you represented B two
years ago - is it a conflict if you now represent A against B?
Similarly if you represented B's son previously is there a conflict by
representing A. So this can become very complicated with Cases,
Litigants, Associates etc. Of course there are degrees of conflict
according to the remoteness of an Associate to any party to a case and
the time elapsed since somebody was represented.

Your original model may not take care of all these problems if infact
it needs to.

Going back to your original model:

I think Client table is more likely ClientCase. If this is so then it
makes sense to have the key of this table in the OpposingInfo table
because you then know who is opposing your client in this case.

There is a strong case as suggested above to have a person table, but
this brings in other issues - like more complex joins and what do you
do when you are adding a person who is already in the Person Table.

Identifying people is always a problem too. If you always have a SS# -
there is no problem because you have a unique identifier, if not and
you have to match on names sometimes there are issues. You'll never
match if there are spelling mistakes (unless you match using a soundex
algorithm - still no certainty), Is the John Smith you are adding the
same John Smith already in the database (even if same address - could
be father and son), is Colin Jones the Collin Jones you already have
and so it goes on.

If you don't have this skills to resolve these issues or dont care
about them then keep to your original model.

to find matching people in each table on SS# -
Select "Client", ss#, FirstName, MiddleName, LastName from ClientInfo
UNION Select "Opposing", ss#, FirstName, MiddleName, LastName from
OpposingInfo where SS# = form!aaa!SS#

In a Union query you must select the same number of fields from each
table. If you want a field from one table that is not in the other use
"" (if it's character or maybe 0 if numeric) in place of the field in
the other Select clause. If for example Opposing table had a field
AssociationType (to identify if the person is the Opponent, related to
the opponent, advocate for the opponent etc but this field isnt in
ClientInfo the above query would become:
Select "Client", ss#, FirstName, MiddleName, LastName, "" as
AssociationType from ClientInfo UNION Select "Opposing", ss#,
FirstName, MiddleName, LastName, AssociationType from OpposingInfo

Save this query as say Query1

Then write another query
Select * from Query1 where SS# = SS or if you want it to use a value
from a form then
Select * from Query1 where SS# = forms!formname! fieldname

You could have the where clause twice in the original query and not
have Query2 but it's simpler if you have the 2.

Also best to have an index on the fields in the tables you are matching
on - index on SS# for sure, also on Name if you want a query that
searches by name instead of SS#

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----