Nd Rpt Based on Main Form & Sub Form Help with Linking

[Guest]

It appears I need to do major redesigning.
1. I do have repeating fields: MP and Party# that ids if the record applies
to the 2nd or 3rd claimant of the same case.
2. I don't have clients in my cases table.
3. Tables: Cases [that contains a field for the Claim and and a field for
the Insured]; Attny[represents the Claimants ;Firm;Contacts[at insurance
company who represent the Insured];Def_Attny [who defend the
insured];Invoice[the Claimant and Insured are invoiced thru their
representatives[Attorneys and Contacts [adjusters]at the insurance company.
3(a) I will have numerous cases with the Attorneys and Adjusters. I
usually only have one case with the Claimant and Insured.
4. Who gets an invoice: The Attny on behalf of the Claimant; The Contact on
behalf of the Insured each get one invoice Unless There are two Claimants
then the Attny gets an Invoice for each Claimant. [e.g.,in an auto accident
with 2 injured parties with 2 different attorneys]
5. THE *RS FILE NO* IDENTIFIES THE CASE.The case may consists on one or more
records [claimants or insureds].
6. THE *CASEID* REPRESENTS THE RECORD.
7. It appears I need to:
(a)make a table named Claimants and a table named Insured.
(b)remove the fields: MP and Party# used for cases involving multi parties.
(c)remove the *InvoiceID* from the cases table [I put it there thinking I
may need to use it as a link to the Invoice table:wrong.]
(d)remove *Claimant* and *Insured* fields from Cases Table.
8. Re: Invoice Table you last wrote about the *RS_File_No*:"No. It doesn't
ID each record...." I mistyped. The *RS_File_No* in the Invoice table is
used to identify each case. Everyone I invoice in a case gets the same
*RS_File_No* as their invoice number and it is used as reference to the case
in correspondence.
The *RS_File_No* is used as the master and child links that links the
Invoice Sub Form and the Cases Form.
9. Before I begin the horror of redesigning my database, do you think once I
have tables one for the Claimant and the othe for the Insured it will solve
my initial problem: inserting the Claimant field in my Case Receips Report?
Thanks for you patience. I remain somewhat muddled.

Joan
--

Structure of tables: The cases tables has so many fields I am only posting
the important ones:

If it's got any repeating fields (Whatsis1, Whatsis2, Whatsis3 or
March, April, May) then it needs redesigning.

Table: Cases
Field Name DataType Desciption
CaseID AutoNo Primary Key: Used to id each
record
Long
integer>increment>Yes(No dups)
RS_File_No Number Used to id each case
Long integer>required
yes>dups ok

Then it DOES NOT ID EACH CASE. That may be the source of your
confusion. The RS_FILE_No is identifying *A CLAIMANT* in a case - not
a case. Right?

InvoiceID Number Used to id each invoice
Long integer>required
no>indexed no

The Cases table *SHOULD NOT* contain an InvoiceID - or it should if
and only if each Case refers to either zero or one invoices, NEVER
MORE.

MP Yes/No Used id if multi Claimants

Unnecessary and should be omitted. If you have a table of claimants
you can just count them.

Party# Number Assigned to additional
Claimants[0,1,2]
Claimant Text Last Name of Claimant
Insured Text Last Name of Insured

And if one case has multiple claimants, THESE FIELDS SHOULD NOT EXIST.
They should be in a *claimants* table, related one to many to this
case table.

Invoice Table:
InvoiceID Auto# Id's each invoice> Primary Key
Long
integer>increment>Yes no dups
RS_File_No Number Id's each record
Long
integer>increment>yes no dups

No. It doesn't ID each record, again unless you have ONLY ONE INVOICE,
*ever*, for a Case. Is that the case? (apologies)

[Is this wrong?]
CaseID Number Key used to link to Cases
long interger>required
no>
indexed Yes dups ok
RELATIONSHIP:
Cases Table Invoicd Table
CaseID CaseID
One to Many Relationship

Question: In the qryCashReceipts using the Cases and Invoice tables, how
should the CaseID be joined? All cases in Invoice and only those in Cases
where joined fields are equal or how? Now I am confused [a word I used
often] about the relationship in the qry and in the Relationship (1-2 many
etc.).
You must be ready to scream.

I am.
Shall await your reply.

OK... let's start with the basics.

Each table represents one type of Entity: a case; a client; a
claimant; an invoice; something that exists in the real world.

Each record in a Table contains information about one, and only one,
member of that class. If a record contains information about two
clients - *that table is incorrectly designed*.

It appears that in the real world situation you have:

Cases

Each Case may have one or more Clients
Each Case may have one or more Claimants

Each Client may be a client for one and only one Case
******OR*******
Each Client may be a client for one or more Cases
(I don't know your business; which is true?)

Each Claimant may be a claimant for one and only one Case
******OR*******
Each Claimant may be a claimant for one or more cases
(again... which is true?)

Each Client in a Case may receive zero, one, or more Invoices
pertaining to that Case.

Is this analysis correct?

John W. Vinson[MVP]

 

[John Vinson]

9. Before I begin the horror of redesigning my database, do you think once I
have tables one for the Claimant and the othe for the Insured it will solve
my initial problem: inserting the Claimant field in my Case Receips Report?
Thanks for you patience. I remain somewhat muddled.

I'm going to try to review your proposed restructure later today or
tomorrow (can't get to it right now). However, having each table
represent information about just one Entity certainly cannot hurt,
especially if you want a report with information about one of those
entities!

John W. Vinson[MVP]

 

[John Vinson]

JoanOC" wrote:

Replying again, I posted this on the website link but it appears not
to have propagated. Apologies if you're getting this twice!


Answers inline.

It appears I need to do major redesigning.
1. I do have repeating fields: MP and Party# that ids if the record applies
to the 2nd or 3rd claimant of the same case.

Someday you will have four claimants... and you're already having
trouble
relating data to a particular claimant. You *do* need a many to many
relationship (using a third table) between cases and claimants, it
appears.

2. I don't have clients in my cases table.

3. Tables: Cases [that contains a field for the Claim and and a field for
the Insured]; Attny[represents the Claimants ;Firm;Contacts[at insurance
company who represent the Insured];Def_Attny [who defend the
insured];Invoice[the Claimant and Insured are invoiced thru their
representatives[Attorneys and Contacts [adjusters]at the insurance company.

see below

3(a) I will have numerous cases with the Attorneys and Adjusters. I
usually only have one case with the Claimant and Insured.

*usually* doesn't cut any ice! It's 5% of the data that makes for 95%
of the
problems. It is really best to design the datastructure so that it
covers all
the cases.

4. Who gets an invoice: The Attny on behalf of the Claimant; The Contact on
behalf of the Insured each get one invoice Unless There are two Claimants
then the Attny gets an Invoice for each Claimant. [e.g.,in an auto accident
with 2 injured parties with 2 different attorneys]

So the Invoice table should - it appears - be joined, not to the Cases
table
directly, but to the many-to-many resolver table linking Cases to
Attorneys.

5. THE *RS FILE NO* IDENTIFIES THE CASE.The case may consists on one or more
records [claimants or insureds].
6. THE *CASEID* REPRESENTS THE RECORD.

ok... so there is a one CaseID to many RS_File_No relationship, right?
The
nomenclature is a bit confusing in that case, because a single Case
may have
multiple CaseID's. That's ok just so long as you are consistant and
understand the meaning (that may have been the source of some of my
confusion
earlier!)

7. It appears I need to:
(a)make a table named Claimants and a table named Insured.
absolutely

(b)remove the fields: MP and Party# used for cases involving multi parties.
yes

(c)remove the *InvoiceID* from the cases table [I put it there thinking I
may need to use it as a link to the Invoice table:wrong.]

It's vice versa - you need to link the Invoices table to the Cases
table, by
CaseID.

(d)remove *Claimant* and *Insured* fields from Cases Table.

likewise, again - the Claimants and Insureds tables should both have a
CaseID as a link.

8. Re: Invoice Table you last wrote about the *RS_File_No*:"No. It doesn't
ID each record...." I mistyped. The *RS_File_No* in the Invoice table is
used to identify each case. Everyone I invoice in a case gets the same
*RS_File_No* as their invoice number and it is used as reference to the case
in correspondence.

So even if there are multiple CaseID's, you can pick up the (common)
RS_File_No from the Cases table to print on the invoice.

The *RS_File_No* is used as the master and child links that links the
Invoice Sub Form and the Cases Form.

Well... I don't think that will work, because an Invoice does not
pertain to
an entire case. It pertains only to one attorney (of several, often)
associated with a case. The proper link - again, if I'm understanding
things
aright - is not the RS_File_No (which is too general) but the CaseID
(which
refers to a specific attorney's relationship with a case). Right?

9. Before I begin the horror of redesigning my database, do you think once I
have tables one for the Claimant and the othe for the Insured it will solve
my initial problem: inserting the Claimant field in my Case Receips Report?
YES.

Thanks for you patience. I remain somewhat muddled.

Hope this helps. (I'm actually on vacation in Texas, so I may not be
able to
reply again until next week).

John W. Vinson

John W. Vinson[MVP]