Table Setup and Reports Suggestions

I am posting in hopes of getting some ideas/insights into how to property
set-up the table to get the correct information out of it. I will only post
the relevant fields.

taDEFENDANTS
DefendantID (PK) (Auto)
CLT (FK) (Long)
DefendantNameID (FK) (Long) to table taDEFENDANTS
TrusteeID (FK) (Long) to table taDEFENDANTS_1
DefendantTypeID (FK) (Long) to table taDEFENDANTTYPE

I have the above table set up in this fashion because of the following
reasons:

Some defendants are banks that have Trust Deeds on properties. Those Trust
Deeds have the Bank as the Beneficiary of the Document and the Property
Owners are granting a property interest to the Trustee. As a result, both
the bank and the trustee each now have an interest in the property.

The Problem I am having:

When I am trying to set up a table for the Service of Process on these
defendants, I am having a hard time dealing with each the Defendant and the
Trustee (If there is one). In essence, I am putting two defendants on the
same row/DefendantID.

The primary I set the table up this way is that there are some reports that
must be submitted to the court that require a listing of the encumbrances
that are found on a property. Each line in the report should represent a
single Trust Deed which would include the Trustee’s Name (if any).

The report, as an example should look like:

Lien Holder Name Date Lien Amount Document Type Instr Number
Trustee
Big City Bank 01/01/1900 $5,000.00 Trust Deed
2009100801 Bob Jim

The Question:

How do I maintain two separate entries in the Defendant table for the same
loan but link those two names together in a single row of information.

My Correction Thought:

My thinking on how to correct his is two fold, leave the table set-up as is.
However, When I enter the Bank as a Defendant, I would also enter the
Trustee Name. However, I would immediately enter the Trustee as a Defendant
in his own right as a separate entry. When I generate the above report I
would change the query to not include Defendants where the
DefendantType=Trustee? However, this seems to be double entry and a waste of
energy and time.

I am open to other suggestions on how to properly adjust my tables to make
this work.

Thanks,

Ray.

I'm not sure about your overall design but it sounds like the issue you are
grappling with is:
1 - I have a table with two foreign keys linking to the same table
(tblDefendant)
2 - how do I write a query that will give me information from this table and
from the two tblDefendant tables?

When doing the query you can add the tblDefendant table into the query twice
(the second instance with have a diff name).
Then join the main table with each of the two tblDefendant tables (probably
left joins).

This is a common practice,
Mark

PS: I apoligize if I might of missed the point of your question.

"RayToddJr" <RayToddJr.RemoveThis@discussions.microsoft.com> wrote in message
news:340F2AB2-DB91-4F49-98ED-4A0620AA3B72@microsoft.com...
>I am posting in hopes of getting some ideas/insights into how to property
> set-up the table to get the correct information out of it. I will only
> post
> the relevant fields.
>
> taDEFENDANTS
> DefendantID (PK) (Auto)
> CLT (FK) (Long)
> DefendantNameID (FK) (Long) to table taDEFENDANTS
> TrusteeID (FK) (Long) to table taDEFENDANTS_1
> DefendantTypeID (FK) (Long) to table taDEFENDANTTYPE
>
> I have the above table set up in this fashion because of the following
> reasons:
>
> Some defendants are banks that have Trust Deeds on properties. Those
> Trust
> Deeds have the Bank as the Beneficiary of the Document and the Property
> Owners are granting a property interest to the Trustee. As a result, both
> the bank and the trustee each now have an interest in the property.
>
> The Problem I am having:
>
> When I am trying to set up a table for the Service of Process on these
> defendants, I am having a hard time dealing with each the Defendant and
> the
> Trustee (If there is one). In essence, I am putting two defendants on the
> same row/DefendantID.
>
> The primary I set the table up this way is that there are some reports
> that
> must be submitted to the court that require a listing of the encumbrances
> that are found on a property. Each line in the report should represent a
> single Trust Deed which would include the Trustee's Name (if any).
>
> The report, as an example should look like:
>
> Lien Holder Name Date Lien Amount Document Type Instr
> Number
> Trustee
> Big City Bank 01/01/1900 $5,000.00 Trust Deed
> 2009100801 Bob Jim
>
> The Question:
>
> How do I maintain two separate entries in the Defendant table for the same
> loan but link those two names together in a single row of information.
>
> My Correction Thought:
>
> My thinking on how to correct his is two fold, leave the table set-up as
> is.
> However, When I enter the Bank as a Defendant, I would also enter the
> Trustee Name. However, I would immediately enter the Trustee as a
> Defendant
> in his own right as a separate entry. When I generate the above report I
> would change the query to not include Defendants where the
> DefendantType=Trustee? However, this seems to be double entry and a waste
> of
> energy and time.
>
> I am open to other suggestions on how to properly adjust my tables to make
> this work.
>
> Thanks,
>
> Ray.
>