G
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
--
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
--
John Vinson said: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.
Well... I *was* using all caps a bit too much there... said: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]