How to design db

J

jmuirman

I have a list of [Contacts]. I would like to choose contacts to populate 3
different fields and am not sure how to do it. For example:

From the [Claim] table I want to populate:
Client
PlaintiffCounsel
DefenseCounsel with my [Contacts] table

So that...I can show the name, address etc by clicking a drop down list

Thanks,

John
 
S

Steve Schapel

John,

One standard approach to this is to have a ContactID (or equivalent)
field as the Primary Key field in the Contacts table. And then in the
Claim table, you can have a ClientID, PlaintiffCounselID, and
DefenceCounselID, all of which can have an entry that relates them to
the Contacts table. As for data entry via your Claim form, each of
these fields can be represented by a combobox, whose Row Source is based
on the Contacts table.

As for then getting to see the corresponding name and address of the
Contacts on the Claim form, there are a number of approaches that can be
taken to this. Some of this is explained in this article...
http://accesstips.datamanagementsolutions.biz/lookup.htm
 
J

jmuirman

I'm a little confused: So would the design look like this:

tContacts tNewCases
ContactId---------- NewCase Id
Company | PlaintiffsCounselID
Name |_________ ContactsId
Address DefenseCounselId
Phone Claimant
Fax DateOfAccident
DateOfBirth
 
S

Steve Schapel

John,

Well, to be more complete...

tContacts tNewCases
ContactId--------| NewCase Id
Company |---------------PlaintiffsCounselID
Name |-----------ContactsId
Address | |--------DefenseCounselId
Phone | | Claimant
Fax | | DateOfAccident
| | DateOfBirth
tContacts | |
ContactId-----------| |
Company |
Name |
... |
|
tContacts |
ContactId-------------|
Company
Name
...

You might also want to have a couple of Yes/No fields in the Contacts
table, that you can use to indicate which category(ies) each Contact
belongs to, and then you can use this to restrict which ones are
included in which comboboxes on your form. For example, I imagine only
a certain subset of the entries in the Contacts table will be applicable
to being entered as the DefenceCounsel.

By the way, as an aside, 'name' is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control or database object.
 
J

jmuirman

Thanks Steve. That's great - clarifies things for me. Now is there any way to
populate Defense, Plaintif and Client from the same table??
 
J

jmuirman

In your example is tContacts one table? Or is there a tContacts table for
Client, Defense and Plaintiff?

In the tNewCase table I added fields for PlaintiffAtty, DefenseAtty and
Client and used the "lookup" option to find values in each of the tables. It
works fine until I tried to make a Query - Out of 46 records in the tNewCase
table, only 10 records showe up in the qNewCase Query...Any suggestions?

Thanks,

John
 
S

Steve Schapel

John,

No there is only one tContacts table. But if you are making a query
where you want to retrieve the contact details (name etc) of the
PlaintiffAtty, DefenseAtty, and Client, you will have to add the
tContacts table to the query 3 times, once for each relationship. And
the Joins in the query will probably need to be Left Joins, unless there
will *always* be valid entries for all three fields PlaintiffAtty,
DefenseAtty, and Client in the tNewCase table.

By the way, I would recommend against using the Lookup Field in the
table design. More information here...
http://www.mvps.org/access/lookupfields.htm

Have a go, and if you still can't get it to behave correctly, please
copy/paste the SQL view of the query into your post here.
 
J

jmuirman

Here's the SQL view - Ultimately I want to be able to choose Plaintiff,Client
and Defense in a form and the see the details in a report. Thanks - this is
really difficult for me for some reason. Thanks,

John

SELECT tNewCases.ID, tNewCases.[Set-up Date], tNewCases.[Claim Number],
tNewCases.Insured, tNewCases.Dependents, tNewCases.[Average Weekly Wage],
tNewCases.TTD, tNewCases.PPD, tNewCases.Diagnosis, tNewCases.[Total File
Authority], tNewCases.Demand, tNewCases.Offer, tNewCases.Claimantfirst,
tNewCases.Claimantlast, tNewCases.claimantAddress, tNewCases.claimantCity,
tNewCases.claimantState, tNewCases.claimantZip, tNewCases.[Date of Birth],
tNewCases.[Date of Accident], tNewCases.[Location of Accident],
tNewCases.Venue, tNewCases.[Life Expectancy], tNewCases.[Arbitrator/Judge],
tNewCases.[Case Number], tNewCases.Verdict, tNewCases.[Current Offer],
tNewCases.[Current Demand], tNewCases.[Pre-trial Opinion],
tNewCases.[Receiving SSDI?], tNewCases.[Co-defendants], tNewCases.[Dependant
Name1], tNewCases.[Dependant Name2], tNewCases.[Dependant Name3],
tNewCases.Budget, tNewCases.[Attorney Fees], tNewCases.[Claimant (Cash)],
tNewCases.[Annuity (Amount)], tNewCases.Comments, tNewCases.[Marital Status],
tNewCases.Gender, tNewCases.[Social Security Number], tNewCases.[Life
Company], tNewCases.[Purchase Date], tNewCases.[Settlement Terms],
tNewCases.Premium, tNewCases.[Administrative Fee], tNewCases.Beneficiary,
tNewCases.Relationship, tNewCases.[Payee (if other than Annuitant)],
tNewCases.[Defendant Carrier], tNewCases.Upfront, tNewCases.[Release
Comments], tNewCases.[Type of Case], tNewCases.Email, tNewCases.[Case Notes],
tNewCases.Notes, tNewCases.[Case Status], tNewCases.[Best Rated Age],
tNewCases.[Acknowledged Referral?], tNewCases.[Diary Date], tNewCases.[To
Do], tNewCases.[SSDI Monthly Income], tNewCases.[Pension Monthly Income],
tNewCases.[Other Monthly Income3], tNewCases.[Other Monthly Income4],
tNewCases.[Date Closed], tNewCases.Broker, tNewCases.[Excess Carrier],
tNewCases.PlaintiffAttyId, tNewCases.DefenseAttyId, tNewCases.ClientId
FROM tNewCases, tContacts;
 
J

John Vinson

Here's the SQL view - Ultimately I want to be able to choose Plaintiff,Client
and Defense in a form and the see the details in a report. Thanks - this is
really difficult for me for some reason. Thanks,
Click to expand...

You're not joining the tNewcases and tContacts tables at all. This
will give you a combined record matching EVERY SINGLE RECORD in each
table with EVERY SINGLE RECORD in the other, without any rhyme or
reason - that is, if you have 100 cases and 60 contacts, you'll see
all 6000 possible pairings.

For a Form, to enter new cases, base the Form on tNewcases (not on a
query joining it to tContacts, just on tNewcases itself). Have three
Combo Boxes on the form; each should be based on tContacts, and one
should be bound to PlaintiffID, another to ClientID, and a third to
DefenseID. Use the combo box wizard - it will conceal the ID's and
display the names.

For a Report, you need a Query. In the query grid, add tNewcases. Then
add tContacts to the query THREE TIMES - Access will call the first
one tContacts, then tContacts_1 and tContacts_2. Join PlaintiffID to
tContacts.ID; ClientID to tContacts_1.ID; and DefenseID to
tContacts_2.ID. Select each join line and view the relationship's
properties; change each to "Show all records in tNewCases and matching
records in..."

Base your Report on this query, picking up the Plaintiff information
from the tContacts instance, Client information from tContacts_1, and
so on.

One question: your current design suggests that you will NEVER have a
case with more than one client, more than one plaintiff, or more than
one defense attorney. I'm no lawyer but I don't think this is a safe
assumption... am I mistaken?

John W. Vinson[MVP]
 
J

jmuirman

Thanks John. You're right - I may have several defense attys. But sometimes
any party may be my client. How do I change the design to provide for this?

Also - I tried to connect the tables and kept getting the message "Type
Mismatch" or something like that. I'm missing a fundamental concept somehow.

Thanks,

John
 
J

jmuirman

Keep getting "Type Mismatch..." error - here's my sql view:

SELECT tNewCases.[Set-up Date], tNewCases.[Claim Number], tNewCases.Insured,
tNewCases.Dependents, tNewCases.[Average Weekly Wage], tNewCases.TTD,
tNewCases.[Total File Authority], tNewCases.Demand, tNewCases.Offer,
tNewCases.Claimantfirst, tNewCases.Claimantlast, tNewCases.claimantAddress,
tNewCases.claimantCity, tNewCases.claimantState, tNewCases.claimantZip,
tNewCases.[Date of Birth], tNewCases.[Date of Birth], tNewCases.[Date of
Accident], tNewCases.[Location of Accident], tNewCases.Venue, tNewCases.[Life
Expectancy], tNewCases.[Arbitrator/Judge], tNewCases.[Case Number],
tNewCases.[Current Offer], tNewCases.[Current Demand], tNewCases.[Receiving
SSDI?], tNewCases.[Co-defendants], tNewCases.[Dependant Name1],
tNewCases.[Dependant Name2], tNewCases.[Dependant Name3], tNewCases.Budget,
tNewCases.[Attorney Fees], tNewCases.[Claimant (Cash)], tNewCases.[Annuity
(Amount)], tNewCases.Comments, tNewCases.[Marital Status], tNewCases.Gender,
tNewCases.[Social Security Number], tNewCases.[Life Company],
tNewCases.[Purchase Date], tNewCases.[Settlement Terms], tNewCases.Premium,
tNewCases.[Administrative Fee], tNewCases.Beneficiary,
tNewCases.Relationship, tNewCases.[Payee (if other than Annuitant)],
tNewCases.[Defendant Carrier], tNewCases.Upfront, tNewCases.[Release
Comments], tNewCases.[Type of Case], tNewCases.Email, tNewCases.[Case Notes],
tNewCases.Notes, tNewCases.[Case Status], tNewCases.[Best Rated Age],
tNewCases.[SSDI Monthly Income], tNewCases.[Pension Monthly Income],
tNewCases.[Other Monthly Income3], tNewCases.[Other Monthly Income4],
tNewCases.Broker, tNewCases.[Excess Carrier], tNewCases.PlaintiffAttyId,
tNewCases.DefenseAttyId, tNewCases.ClientId
FROM ((tNewCases LEFT JOIN tContacts ON tNewCases.PlaintiffAttyId =
tContacts.[Client ID]) LEFT JOIN tContacts AS tContacts_1 ON
tNewCases.ClientId = tContacts_1.[Client ID]) LEFT JOIN tContacts AS
tContacts_2 ON tNewCases.DefenseAttyId = tContacts_2.[Client ID];
 
S

Steve Schapel

John,

I suspect the Type Mismatch is related to you using a Lookup field in
your table. Did you read the article I referred you to earlier? Now
that John Vinson has joined the discussion, he may feel moved to say
more about this <g>. One of the problems with Lookup Fields is they
obfuscate the actual data in the field by displaying something else.
You need to make sure the PlaintiffAttyId (for example) field in the
tNewCases table is the same data and data type as the [Client ID] field
in the tContacts table.
 
J

jmuirman

Thanks Steve. I read the memo, but I thought it applied to the
PlaintiffId-type fields. So I eliminated all of the other look-up wizard
fields - but I still get the mismatch message. Maybe there is something in
the "join" properties that I'm not doing right. Very frustrating - it
shouldn't be this difficult.

Thanks for your help.

John
 
S

Steve Schapel

John,

Sorry you are having such a struggle with this.

Look in the design view of the tables. What is the Data Type of the
PlaintiffAttyId, ClientId, and DefenseAttyId fields in the tNewCases
table, and also the Client ID field in the tContacts table?
 
Top