Use one table to represent two categories of the same record

[west]

I'm creating a legal form that contains:




Case Specifics:

• Case Number
• Office Number
• Court Location
• Other Part Lawyer
• Lawyer Phone Number
• Email

Plaintiff Info:

• Name
• Address
• Phone
• email

Defendant Info:

• Name
• Address
• Phone
• email

The tables:

tblMain:

• tblMainCaseID – AutoNumber - Key
• tblMainCaseNo – Number
• tblMainOfficeNo – Number
• tblMainLocation – Text
• tblMainLawyer – Text
• tblMainLawyerPhone – Text
• tblMainEmail – Hyperlink
• tblClientsID – Number

tblClients

• tblClientsID – AutoNumber – Key
• tblClientsName
• tblClientsAddress
• tblClientsPhone
• tblClientsEmail

Relations: tblClients_tblClientsID – tblMain_tblClientsID – One to Many


Now, a client can be Defendant in a case, and a Plaintiff in another, it
doesn't make sense to make two tables, the same client could be duplicaded
that way. But since I have a single client table to represent both Defendants
and Plaintiffs I could create a Plaintiffs section on the mainform to be
controled by lookup technique but I can't do the same for the Defendants and
the Main form needs sections to represent both.


I tried creating two queries of tblClients with different names and then creating
Plaintiffs and Defendants sections based on the queries but it didn't work.


I also tried ducplicating the Plaintiffs section in the main form and then changing
its control source, but I didn't even finish. It gave me a few errors.


Any thoughts?


-JC

[MAF4Fam6]

I tried creating two queries of tblClients with different names and then creating Plaintiffs and Defendants sections based on the queries but it didn't work.

I also tried ducplicating the Plaintiffs section in the main form and then changing its control source, but I didn't even finish. It gave me a few errors.

You are right in keeping ALL the clients in ONE Table.
However...if I understand you correctly:
Based on the fields you provided from the tblClients Table:
How are you distinguishing whether a client is a Plaintiff, Defendant, or both?
Personally, I would create two additional fields in the tblClients Table:

blnPlaintiff - Yes/No
blnDefendant - Yes/No

As you create new clients and manage existing ones, you can use the Check Boxes to active/deactivate their client status.
The values from the two boolean fields will enable you to create seperate or all-inclusive Queries on your clients.

-RC

[west]

I like your idea... and if I were designing a one class per section in
the main form I'd go by this alternative. But both Plaintiff and
Defendant has a labeled section on the Main form. What I'm
trying to do is to use the lookup technique in a combo box for both
Plaintiff and Defendant. I found out that with the lookup
alternative I needed:

tblClientsIDa
tblClientsIDb

In tblMain, then add tblClients again in the Relationships Tools manager,
wich is going to create a tblClients_1 copy, and link:

tblClients_tblClients to tblMain_tblClientsIDa
tblClients_tblClients_01 to tblMain_tblClientsIDb

This worked fine. But I'm still trying to figure out how to relate the
rest of the controls to each Plaintiff's and Defendant's section:
Address, Phone, email as these controls are going to be present
for both.

I appreciate everyone's time. ... and if I find a solution I'll post back.

Thanks to all.

-JC