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

[Guest]

I have an Invoice Sub Form [record source is Invoice table]
that is linked to my Cases Form [my Main Form record source is Cases Query]by
RS_File_No
LinkChild Field: RS_File_No
LinkMasterField: RS_File_No
When I run my Cash Receipts Report I want to include one field from my Cases
Query named: Claimant
I have tried everything to do this for over a year without success. Help
appreciated.

 

[John Vinson]

I have an Invoice Sub Form [record source is Invoice table]
that is linked to my Cases Form [my Main Form record source is Cases Query]by
RS_File_No
LinkChild Field: RS_File_No
LinkMasterField: RS_File_No
When I run my Cash Receipts Report I want to include one field from my Cases
Query named: Claimant
I have tried everything to do this for over a year without success. Help
appreciated.

What is there about RS_File_No that would allow Access (or me, or you)
to identify the Claimant? What is the Recordsource of the Cash
Receipts Report?

REmember - YOU know the structure of your data. You can see your
screen. We cannot; and my telepathy seems to be a bit on the blink
this evening...


Well, it's giving me a nudge. Put the Control Source of a textbox on
your report to

=DLookUp("[Claimant]", "[Cases Query]", "[RS_File_No] = " &
[RS_File_No])

or, if RS_File_No is of Text datatype,

=DLookUp("[Claimant]", "[Cases Query]", "[RS_File_No] = '" &
[RS_File_No] & "'")


John W. Vinson[MVP]

 

[Guest]

Ans. to your first question: Each claimant has a CaseId# and 95%also have
their own RS_File_No. The RS_File_No identifies the case, but about 5% have
multiple claimants who each have the same RS_File_No but each have their own
CaseID#.
The Cases table has a caseid# as does the Invoice table.
RS_File_No is a numeric field. CaseID# is auto numeric field.
I will try to figure out what you suggested.
Why can't I join the Cases qry (or table) with the Invoice table by the
RS_File_No.?
I have 2 of your books - it there anything in them that would help? There at
work and I can't remember the titles.
Is it because the Invoice is a subform that the caseid# can't be joined with
the caseid# in Cases?
--
Joan

I have an Invoice Sub Form [record source is Invoice table]
that is linked to my Cases Form [my Main Form record source is Cases Query]by
RS_File_No
LinkChild Field: RS_File_No
LinkMasterField: RS_File_No
When I run my Cash Receipts Report I want to include one field from my Cases
Query named: Claimant
I have tried everything to do this for over a year without success. Help
appreciated.

What is there about RS_File_No that would allow Access (or me, or you)
to identify the Claimant? What is the Recordsource of the Cash
Receipts Report?

REmember - YOU know the structure of your data. You can see your
screen. We cannot; and my telepathy seems to be a bit on the blink
this evening...


Well, it's giving me a nudge. Put the Control Source of a textbox on
your report to

=DLookUp("[Claimant]", "[Cases Query]", "[RS_File_No] = " &
[RS_File_No])

or, if RS_File_No is of Text datatype,

=DLookUp("[Claimant]", "[Cases Query]", "[RS_File_No] = '" &
[RS_File_No] & "'")


John W. Vinson[MVP]

 

[John Vinson]

Ans. to your first question: Each claimant has a CaseId# and 95%also have
their own RS_File_No. The RS_File_No identifies the case, but about 5% have
multiple claimants who each have the same RS_File_No but each have their own
CaseID#.
The Cases table has a caseid# as does the Invoice table.
RS_File_No is a numeric field. CaseID# is auto numeric field.
I will try to figure out what you suggested.
Why can't I join the Cases qry (or table) with the Invoice table by the
RS_File_No.?

Because it's not unique.

I have 2 of your books - it there anything in them that would help? There at
work and I can't remember the titles.

I haven't written any books. You're probably thinking about my
esteemed colleague John Viescas.

Is it because the Invoice is a subform that the caseid# can't be joined with
the caseid# in Cases?

The Invoice

IS NOT A SUBFORM.

Data is not stored in Forms. It's not stored in Subforms.

Data is stored in Tables; forms are tools, windows which let you
display data stored in tables.

I don't understand your table structure fully. What tables do you
have? How are they related?

John W. Vinson[MVP]

 

[Guest]

I have the following Tables in my CasesRS Database:
Attny;Calls;Carriers;Cases;Contacts;D_Attny;D_Firm;Invoice;N_Attny;N_Firm;Status;
Tickler;Update
I realize the data is stored in the Tables, but the confusion arises when I
am trying to
join them for a report as in this case. Feel stupid. I am not thinking thru.
Other: You were the person who told be about the books written by John V.
when I asked if there were any good Access books available.
Shall await your reply.
Joan

 

[Guest]

More:
the qryCashReceipts is based on my Invoice Table.
My Cash Receipts Report is based on my qryCashReceipts.
That is fine. Problem arises when I try to add my Cases Table to my
qryCashReceipts.
Problem:shows duplicates Only of multi party cases.
I realize that the RS_File_No is not an unique field. The auto unique field
in Cases and Invoice tables doesn't work either.
Do I need to add another field to both my invoice and cases tables to join?

 

[John Vinson]

More:
the qryCashReceipts is based on my Invoice Table.
My Cash Receipts Report is based on my qryCashReceipts.
That is fine. Problem arises when I try to add my Cases Table to my
qryCashReceipts.
Problem:shows duplicates Only of multi party cases.

Exactly. That's precisely how queries are designed to work. If you
join two tables in a one to many relationship (Cases and Invoices)
you'll get each record in the "one" side table repeated as many times
as there are records in the "many" side table.

I realize that the RS_File_No is not an unique field. The auto unique field
in Cases and Invoice tables doesn't work either.

Each table should have a unique Primary Key (autonumber or other)
which uniquely identifies each record in that table. It sounds like
you have that - perhaps you have *both* a unique Autonumber field and
also a unique RS_File_No field, I don't know.

Do I need to add another field to both my invoice and cases tables to join?

One case would have multiple invoices, at least potentially, would it
not? You shouldn't need any other fields, provided each "one" side
table in a relationship has a unique primary key field, and each
related "many" side table has a field of matching datatype (Long
Integer if you're matching an Autonumber) as a "foreign key" to the
one side table.

John W. Vinson[MVP]

 

[Guest]

Cases Table: Primary Key: CaseID# auto number>long integer>Increment>No dups
Invoice Table: The same as above.
Yes one case sometimes does have multiple invoices:The case has one
RS_File_No, but the case may be comprised of 2 or more parties that each
receive their own invoice.
Need to study what you just wrote. Somewhat confused as to why I am not able
to join the CaseID#.
The RS_File_No is the same for each invoice in a multi party case. Could
that be a problem?
How should I join the CaseID#s? I have tried all 3 choices w/out success.
Don't give up, please.

 

[John Vinson]

Cases Table: Primary Key: CaseID# auto number>long integer>Increment>No dups
Invoice Table: The same as above.

CaseID# is the primary key of Invoice? That's GOT to be wrong.

Yes one case sometimes does have multiple invoices:The case has one
RS_File_No, but the case may be comprised of 2 or more parties that each
receive their own invoice.
Need to study what you just wrote. Somewhat confused as to why I am not able
to join the CaseID#.

If by "the same as above" you mean that the Primary Key of Invoice
Table is CaseID#, Autonumber, then that IS the problem. You CANNOT use
an autonumber as a foreign key, and - unless each Case# has one and
only one invoice, ever - the CaseID# should not be the Primary Key.
You should have an InvoiceNo or InvoiceID field, with a (separate)
long integer CaseID (I'd avoid using the special character # in
fieldnames) as a link to the Cases table.

The RS_File_No is the same for each invoice in a multi party case. Could
that be a problem?

Yes, of course. Because that will give you each invoice as many
repeats as there are parties, if you join that table.

How should I join the CaseID#s? I have tried all 3 choices w/out success.
Don't give up, please.

I'm STILL trying to figure out how all your tables are related. You
haven't said.

John W. Vinson[MVP]

 

[Guest]

I created an InvoiceID field in the Cases and Invoice Table.
They each are number>long integer>no dups fields.
I have filled in the field starting at number 1 in the Invoice Table.
I am unsure if I need to do this as well in the Cases Table. My guess is
that I do.
Link:
I still have the Cases and Invoice Tables linked by the RS_File_No because
when I tried to use the InvoiceID field it didn't work. Do I need to link
both fields to one another?
I obviously am not thinking thru what you instructed or worse yet, am just
not comphrending the theory.
Relationship: I still have the Cases Table and Invoice Table joined by the
RS_File_No >Indeterminate>only included rows where joined fields from both
tables are equal.
I know this is incorrect. Please don't tear you hair out in frustration. I
seem to have a big mental block here.
Shall await your reply.
I

 

[John Vinson]

I created an InvoiceID field in the Cases and Invoice Table.

Sorry, Joan - that is INCORRECT.

That would mean that each Case can have one, and only one, invoice -
the one referenced in that field.

To join two tables you must put the foreign key in the "many" side
table. That is, if each Case can have zero, one, or more Invoices, the
Invoice table would have an InvoiceID as a Primary Key, and a
non-unique CaseID as a link to the Cases table.

They each are number>long integer>no dups fields.

Again... that's fine for the InvoiceID in the Invoices table (that's
what the primary key is) but not for the Cases table.

I have filled in the field starting at number 1 in the Invoice Table.

This process can be automated if you wish.

I am unsure if I need to do this as well in the Cases Table. My guess is
that I do.
Link:
I still have the Cases and Invoice Tables linked by the RS_File_No because
when I tried to use the InvoiceID field it didn't work. Do I need to link
both fields to one another?

NO.

The link should be from the Primary Key of the Cases table - whatever
uniquely identifies a Case - to the corresponding (CaseID??) field in
the Invoices table.

I obviously am not thinking thru what you instructed or worse yet, am just
not comphrending the theory.

The theory does have a steep learning curve... I'll try to set some
pitons and let down some climbing ropes to help you up it though

Relationship: I still have the Cases Table and Invoice Table joined by the
RS_File_No >Indeterminate>only included rows where joined fields from both
tables are equal.

I know this is incorrect. Please don't tear you hair out in frustration. I
seem to have a big mental block here.

It seems that the RS_File_No IS NOT the primary key of the Cases
table. What you need for the join *is* the Primary Key.

Let's try something else:

In the real world (not in a database), what does RS_File_No represent?
In the real world, what constitutes a Case?
In the real world, what is it that you are invoicing? A case? a client
within a case? something else?

You might want to check out the Database Design 101 link at Jeff
Conrad's excellent reference site:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101


John W. Vinson[MVP]

 

[Guest]

Ans. to "In a real world" questions.
1. RS_File_No represents the number assigned to a case.
2. A case constitutes a civil dispute involving one or more parties.
3. I am invoicing one or more parties involved in a case.
Other:
Relationship: In the toolbar when I click on the relationship icon that
opens up all the tables and how the Invoice table and Cases table are joined
should it be a one to many relationship? Should the fields by CasesID in the
Cases table and InvoiceID in the Invoice table?
Fields: InvoiceID is now the primary key in the Invoice Table. It also
contains a CaseID field.
In the Cases table the CaseID is the primary key.
When I try to join the CaseID fields it does in the not work in the
qryCashReceipts. There are a different number of invoices than cases. The
parties involved in a case do not always get an invoice. So when I try to
link the caseid fields I get the wrong data e.g.,the name of the Claimant
does not match the RS_File_No.
Am I beyond hope?
What is a foreign key?
I am getting the relationship and the master and child link confused.
Right now my brain feels like scrambled eggs. I shouldn't look at this so
late at night. Want to find a solution. Want to understand. I will check
out the site you suggested. Shall, hopefully, hear from you.

 

[John Vinson]

Ans. to "In a real world" questions.
1. RS_File_No represents the number assigned to a case.

Then it should be unique. Otherwise you have two records in the table
which refer to the same case, with different information, but the same
RS_File_No. Will the real Case please stand up?

2. A case constitutes a civil dispute involving one or more parties.
3. I am invoicing one or more parties involved in a case.
Other:
Relationship: In the toolbar when I click on the relationship icon that
opens up all the tables and how the Invoice table and Cases table are joined
should it be a one to many relationship? Should the fields by CasesID in the
Cases table and InvoiceID in the Invoice table?
Fields: InvoiceID is now the primary key in the Invoice Table. It also
contains a CaseID field.
In the Cases table the CaseID is the primary key.

Then in the Relationships window you should delete any existing
relationships between the Cases and Invoices table; drag the CaseID
from the Cases table to the CaseID in the Relationships table.

It's not magic. If the CaseID is 345, then every Invoice pertaining to
that case has a CaseID value of 345. That's how Access can tell to
which case this invoice pertains. These invoices will all have
different invoice ID's - you wouldn't want to assume that Invoice
Number 3419 pertains to case 3149! The InvoiceID identifies the
Invoice; the CaseID identifies the case.

When I try to join the CaseID fields it does in the not work in the
qryCashReceipts. There are a different number of invoices than cases.

I was assuming that would be true. Normally anything you purchase has
one OR MORE invoices.

The parties involved in a case do not always get an invoice. So when I try to
link the caseid fields I get the wrong data e.g.,the name of the Claimant
does not match the RS_File_No.
Am I beyond hope?

Not at all! I may be, because I'm not correctly visualizing your data
structure, and/or not succeeding in communicating my ideas.

What is a foreign key?

Let's use a completely separate classic example: a simple school
enrollment database.

Each Student is enrolled in just one Class (this is an elementary
school, we'll talk about the more realistic many to many relationship
later). Each Class has many students.

The Tables for this would have structures like:

Classes
ClassID <Primary Key>
TeacherLastName
TeacherFirstName
RoomNumber

Students
StudentID <Primary Key>
LastName
FirstName
ClassID <the Foreign Key>

The ClassID in the Students table will be the same for all of the
students in Mrs. McGillicuddy's 3rd grade class; if that is class
number 11, then the ClassID will be 11 for Johnny and Mary and Anita
and all the other students in that class.

I am getting the relationship and the master and child link confused.
Right now my brain feels like scrambled eggs. I shouldn't look at this so
late at night. Want to find a solution. Want to understand. I will check
out the site you suggested. Shall, hopefully, hear from you.

The Master Link Field is the Primary Key.
The Child Link Field is the Foreign Key.

Primary and Foreign Key are terms which apply to fields in Tables;
Master and Child Link Fields are properties of a Subform. They will
contain the names of the corresponding table fields.

John W. Vinson[MVP]

 

[Guest]

RS_File_No cannot be unique because if there are 2 parties involved in one
case they each need there own record (CaseID:unique) and the RS_File_No that
identifies the case.
ClaimantLN: Brown RS_File_No: 2500 AttorneyLN: Callahan CaseID: 350
ClaimantLN: Red RS_File_No: 2500 AttorneyLN: Help CaseID: 351
InsuredLN: Blue RS_File_No: 2500 AdjusterLN: Browning: 350 and
351
The dispute is between 2 plaintiffs and 1 insurance company
When I invoice each attorney [if the case settles] and the insurance company
they each have the same invoice number because I use the RS_File_No 2500 for
the invoice number
I shall study your reply when I return from work. Maybe the above will give
you betterinsight into my crazy database.
- -
Joan

 

[John Vinson]

RS_File_No cannot be unique because if there are 2 parties involved in one
case they each need there own record (CaseID:unique) and the RS_File_No that
identifies the case.
ClaimantLN: Brown RS_File_No: 2500 AttorneyLN: Callahan CaseID: 350
ClaimantLN: Red RS_File_No: 2500 AttorneyLN: Help CaseID: 351
InsuredLN: Blue RS_File_No: 2500 AdjusterLN: Browning: 350 and
351
The dispute is between 2 plaintiffs and 1 insurance company
When I invoice each attorney [if the case settles] and the insurance company
they each have the same invoice number because I use the RS_File_No 2500 for
the invoice number
I shall study your reply when I return from work. Maybe the above will give
you betterinsight into my crazy database.

Ok, so CaseID 350 needs one invoice; CaseID 351 neds a different
invoice. If RS_File_No does not uniquely identify the activity which
needs an invoice, *DON'T USE IT* as a foreign key in the invoice -
because it doesn't link to the correct information!

It appears that CaseID does. Can you use *it* instead?

John W. Vinson[MVP]

 

[Guest]

I'll try again to use the CaseID. Somehow it keeps duplicating as I mentioned
before.
Other: "Will the real case please stand up."
Two fields I use so I will know which party of a multi party case with the
same RS_File_No are:
MP (multi party): Yes/No field Party#: 0,1,2
In Cases Table I have CaseID that is auto number to id the record
and RS_File_No the id the case I also created an InvoiceID number no dups
field.
In Invoice table the primary key is the InvoiceID number no dups
I also have a RS_File_No and a CaseID number. Maybe I should try to join the
InvoiceID.
One could say that I am really confused. --
Joan

RS_File_No cannot be unique because if there are 2 parties involved in one
case they each need there own record (CaseID:unique) and the RS_File_No that
identifies the case.
ClaimantLN: Brown RS_File_No: 2500 AttorneyLN: Callahan CaseID: 350
ClaimantLN: Red RS_File_No: 2500 AttorneyLN: Help CaseID: 351
InsuredLN: Blue RS_File_No: 2500 AdjusterLN: Browning: 350 and
351
The dispute is between 2 plaintiffs and 1 insurance company
When I invoice each attorney [if the case settles] and the insurance company
they each have the same invoice number because I use the RS_File_No 2500 for
the invoice number
I shall study your reply when I return from work. Maybe the above will give
you betterinsight into my crazy database.

Ok, so CaseID 350 needs one invoice; CaseID 351 neds a different
invoice. If RS_File_No does not uniquely identify the activity which
needs an invoice, *DON'T USE IT* as a foreign key in the invoice -
because it doesn't link to the correct information!

It appears that CaseID does. Can you use *it* instead?

John W. Vinson[MVP]

 

[Guest]

I give up. I will enter the name of the claimant in the invoice table each
time I enter a record. Frustrating! Thanks for trying to help.
--
Joan

I'll try again to use the CaseID. Somehow it keeps duplicating as I mentioned
before.
Other: "Will the real case please stand up."
Two fields I use so I will know which party of a multi party case with the
same RS_File_No are:
MP (multi party): Yes/No field Party#: 0,1,2
In Cases Table I have CaseID that is auto number to id the record
and RS_File_No the id the case I also created an InvoiceID number no dups
field.
In Invoice table the primary key is the InvoiceID number no dups
I also have a RS_File_No and a CaseID number. Maybe I should try to join the
InvoiceID.
One could say that I am really confused. --
Joan

RS_File_No cannot be unique because if there are 2 parties involved in one
case they each need there own record (CaseID:unique) and the RS_File_No that
identifies the case.
ClaimantLN: Brown RS_File_No: 2500 AttorneyLN: Callahan CaseID: 350
ClaimantLN: Red RS_File_No: 2500 AttorneyLN: Help CaseID: 351
InsuredLN: Blue RS_File_No: 2500 AdjusterLN: Browning: 350 and
351
The dispute is between 2 plaintiffs and 1 insurance company
When I invoice each attorney [if the case settles] and the insurance company
they each have the same invoice number because I use the RS_File_No 2500 for
the invoice number
I shall study your reply when I return from work. Maybe the above will give
you betterinsight into my crazy database.

Ok, so CaseID 350 needs one invoice; CaseID 351 neds a different
invoice. If RS_File_No does not uniquely identify the activity which
needs an invoice, *DON'T USE IT* as a foreign key in the invoice -
because it doesn't link to the correct information!

It appears that CaseID does. Can you use *it* instead?

John W. Vinson[MVP]

 

[John Vinson]

I give up. I will enter the name of the claimant in the invoice table each
time I enter a record. Frustrating! Thanks for trying to help.

Joan, I do want to help. And your problem can be solved.

One thing that would help me understand and offer better advice is:

Please post the structure of your tables in the format

Tablename
Fieldname Datatype
Fieldname Datatype
Fieldname Datatype

Tablename
Fieldname Datatype
<etc>

and indicate which field is the Primary Key of each table. Also
indicate how the tables are related in your relationships window.


John W. Vinson[MVP]

 

[Guest]

Structure of tables: The cases tables has so many fields I am only posting
the important ones:
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
InvoiceID Number Used to id each invoice
Long integer>required
no>indexed no
MP Yes/No Used id if multi Claimants
Party# Number Assigned to additional
Claimants[0,1,2]
Claimant Text Last Name of Claimant
Insured Text Last Name of Insured

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
[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.

 

[John Vinson]

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]