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

 

[Mr. B]

Your main table needs to have a PlantifID field that is the FK (foreign key) value form the Client's table and a DefendantID field that is also a FK value from the clients table. You will simply need to specify each on your form. The fact that the "clients" table is named Clients may be a little misleading as you will have records in the table for entities (persons or companies) that are not actually your clients. You might want to include a field in the clients table that will allow you to actually identify an entry as actually being your client. This way you could easily identify your clients.

 

[west]

Thanks for the time Mr. B

-quote-
you will have records in the table for entities (persons or companies)
that are not actually your clients.
-/quote-

You are right about that, that issue its been addressed too. But its a
little simpler than this other matter.

I understand the FK concept, but how am I going to create the relationship?,
or, is it not necessary to create any relationship after the FK fields have been
created?

Thanks a lot.

-JC

 

[Mr. B]

If you want to create a relationship you can add the table twice when creating your relationship, but it is not absolutely necessary to even create the relationship in order to accoumplish what you want. You just create a combo box on your form for the Plantiff and and one for the Defendent. These combo boxes will have the PK of the Clients table as the bound column. You will hide the bound column and display the actual name. When a selection is made from the combo box this will populate the field in your main table with the link value to the clients.