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]

Re: Access 03 query question


"Robert" <sanfordlaw@*** inebraska.com> wrote in message
news:1148436822 _20037@sp6iad.s uperfeed.net...[color=blue]
>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.[/color]

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]

Re: Access 03 query question

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[color=blue]
>
> 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
>[/color]



----== 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]

Re: Access 03 query question

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:1148436822 _20037@sp6iad.s uperfeed.net...[color=blue]
>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
> =----[/color]

[Keith Wilby]

Re: Access 03 query question

"Robert" <sanfordlaw@*** inebraska.com> wrote in message
news:1148436822 _20037@sp6iad.s uperfeed.net...[color=blue]
>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)
>[/color]

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.

https://www.keithwilby.com

[Jeff Smith]

Re: Access 03 query question


"Robert" <sanfordlaw@*** inebraska.com> wrote in message
news:1148443657 _20581@sp6iad.s uperfeed.net...[color=blue]
> 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.[/color]

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]

Re: Access 03 query question

Robert wrote:[color=blue]
> 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:[/color]


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]

Re: Access 03 query question

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]

Re: Access 03 query question

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" <NoSpam@Not.Thi s.Address> wrote in message
news:e51b8p$i10 $1@lust.ihug.co .nz...[color=blue]
>
> "Robert" <sanfordlaw@*** inebraska.com> wrote in message
> news:1148443657 _20581@sp6iad.s uperfeed.net...[color=green]
>> 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.[/color]
>
> 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
>
>
>[/color]



----== 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]

Re: Access 03 query question

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" <cjb_kjb@hotmai l.com> wrote in message
news:1148524297 .989421.228920@ y43g2000cwc.goo glegroups.com.. .[color=blue]
> 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#
>[/color]



----== 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 =----

[cjb_kjb]

Re: Access 03 query question

> So much for a simple project. lol. Everyone has given some great[color=blue]
> suggestions and things to think about. I really appreciate the input.[/color]
Like I said - once you start thinking about these things and asking the
hard questions something simple starts to take on gigantic proportions.

In this case dont build the Titanic - be aware of what a simple system
will do and wont do for you. The more complex you make it - the
development time grows exponentially and it could take ages to
complete. Better a simple system than none at all.

ClientCase: This allows you to identify each case for each client
separately. I would say that in the majority of cases (circumstances)
this would be 1 to 1. If you relate opponents to ClientCase rather
than Client you also know which cases they opposed you client in. I
don't know if this is important - but it might be.
[color=blue]
> 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.[/color]

You can have a Client table which would have Client "A" in it.

In ClientCase you would have (Client, Case, other fields....)
"A", "Divorce from 'B'", ......, $1000
"A", "Lease Agreement Violation against 'C'", ......., $400

This brings in another complication. If C is suing A and B (because if
they are divorcing - presumable they both signed the lease at some
stage is "B" also your client - thus

"B", "Lease Agreement Violation against 'C'", ......., $400

or is the client in this case "A & B" - ie 1 entry not 2.

And is there now a conflict of interest.

So now we have a Client table, a ClientCase Table and a Case table (
because a client can have many cases and a Case (depending on how you
interpret it) may have many Clients. (There is a difference if A and B
are separate clients for the Lease case or they are considered a single
client "A & B". In the later case there is only ever one client for a
case so ClientCase and Case can be merged into one table).

Also you need to consider the Opponents table that you wanted.
Assuming multiple clients per case possible then is an opponent an
opponent to the case or an opponent to a particular client for a case.
Could someone just be giving evidence against A in the Lease case but
not B. You must decide this before you can determine if Opponents is
related to Case or ClientCase (or maybe both).


Then the other question with regard to this is if this[color=blue]
> 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?
>[/color]

Yes that should be possible. If you have the fee in the ClientCase
record then you can easily group all fees for a given Clent.

So in the above example if the divorce costs A $1000 and the lease case
costs $800 split $400 each between A and B then if you have the cost to
each in the clientcase records then:

Select client, cost from ClientCase will give you:
A $1000
A $400

B $400

or Select Client sum(cost) from ClientCase Group By Client - will give

A $1400
B $400


I think you may now get an appreciation of the importance of data
analysis. There is no right or wrong answer to a problem. Data
Analysis is building your business rules into a model that will support
an application and hence your business. So the correct model is the
one that enables you to get the answers from the system that you need.
Sometimes there has to be a compromise because the cost of doing a 100%
solution may be too great. In this case you just need to be aware of
what the system is not going to do and allow for that in how you run
your business.

The principles behind data modelling are quite simple - their
application is somewaht more difficult. It's an area that a lot of IT
people dont do very well and most likely the reason for 90% of
application failures. I would say it's the main reason some people say
Access is 'a load of crap'. Nothing wrong with Access - just most
users dont know how to get their data right so they build bad
applications.

good luck

Colin
[color=blue]
> I should have stopped when this was just a thought in the ol' noodle.
>
>
> "cjb_kjb" <cjb_kjb@hotmai l.com> wrote in message
> news:1148524297 .989421.228920@ y43g2000cwc.goo glegroups.com.. .[color=green]
> > 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#
> >[/color]
>
>
>
> ----== 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 =----[/color]