Need Additional Subform Link

[Guest]

My status subform data source is the ClientID# that works fine with one
Exception:
When there are 2 clients (records)that have the same ClientID# (clients are
children so if there are 2 children they are each assigned the same ClientID#
with the 2nd ClientID# having a separate text field that in which the letter
"a" is entered to distinguish one child's records from the other.
e.g.: Client ID#: 2144 assigned to record of one child
ClientID#: 2144 (a) assigned to record of 2nd child
Problem:
When the attny enters data into the status subform of ClientID# it also is
entered into the status subform of the 2nd child because of the way it is
linked.
Q:How do I include the (a) field in the Master Child Link Properties?
Qo I need to change the (a) to a number field?
Help Needed.

-
Joan

 

[John Vinson]

Q:How do I include the (a) field in the Master Child Link Properties?

Include its fieldname separated by semicolons:

ClientID;ClientSuffix

Qo I need to change the (a) to a number field?

No, it just needs to be the same datatype in both tables.

John W. Vinson[MVP]

 

[Guest]

ok. I will need to change to "a" to a numerial:that is what the ClientCase#is.
If that is wrong I'll soon find out.
Other: Purchased Building Microsoft Access Applications by John Viescas.
The store didn't have the one you suggested;I'll get it later. I really need
to understand more theory.
Thanks for your help.

 

[Guest]

I changed the field named TwoFiles from an text field to a number field.
Instead of an "a" in that field I now enter "2" for the second child's record
that has the same ClientID#.
I added to the Master and Child Link the TwoFiles field separated by a
semi-colon.
Result: I can only enter and save data in the second record that contains
the number 2 in the Two Files field.
The entries are no long duplicated in both records, and I am able to type
data in the first record, but cannot save it.
Problem now is how to enter and save data in the first record.
Hope it is doable.
Joan

 

[John Vinson]

I changed the field named TwoFiles from an text field to a number field.
Instead of an "a" in that field I now enter "2" for the second child's record
that has the same ClientID#.
I added to the Master and Child Link the TwoFiles field separated by a
semi-colon.
Result: I can only enter and save data in the second record that contains
the number 2 in the Two Files field.
The entries are no long duplicated in both records, and I am able to type
data in the first record, but cannot save it.
Problem now is how to enter and save data in the first record.

The Master and Child fields NEED TO MATCH.

TwoFiles can be either text or number, it makes *absolutely no
difference*; they do need to match in the two tables.

Try having fields ClientID and TwoFiles in both tables; they should be
a joint, two-field Primary Key in the "one" side table of the
relationship. Make the Master and Child Link FIeld properties of the
subform

ClientID;TwoFiles


John W. Vinson[MVP]

 

[Guest]

1.

The Master and Child fields NEED TO MATCH.

TwoFiles can be either text or number, it makes *absolutely no
difference*; they do need to match in the two tables.

Try having fields ClientID and TwoFiles in both tables; they should be
a joint, two-field Primary Key in the "one" side table of the
relationship. Make the Master and Child Link FIeld properties of the
subform

ClientID;TwoFiles


John W. Vinson[MVP]

 

[Guest]

1. The Master and Child fields match.
2. TwoFiles and ClientCaseID# are both numbers.
3. ClientCaseID# and TwoFiles fields are in both the Cases and Notes tables.
4. ClientID [not to be confused with the ClientCaseID# field] is the
primary key in cases table; NotesID is the primary key in the Notes table.
I am unsure what a two-field Prmary key is.
I noticed the following when I looked at the Notes subform in cases form:
ClientCaseID#2224: there were no notes [1st record in cases]
#2224 "2" [the second record in cases] there were notes
entered.
Thanks for your patience. You usually are able to get thru to me even though
it does take some doing. I really apprecate your assistance.
Joan

 

[John Vinson]

1. The Master and Child fields match.
2. TwoFiles and ClientCaseID# are both numbers.
3. ClientCaseID# and TwoFiles fields are in both the Cases and Notes tables.
4. ClientID [not to be confused with the ClientCaseID# field] is the
primary key in cases table; NotesID is the primary key in the Notes table.
I am unsure what a two-field Prmary key is.
I noticed the following when I looked at the Notes subform in cases form:
ClientCaseID#2224: there were no notes [1st record in cases]
#2224 "2" [the second record in cases] there were notes
entered.
Thanks for your patience. You usually are able to get thru to me even though
it does take some doing. I really apprecate your assistance.
Joan

IT sounds like the Master/Child Link Field should be

ClientCaseID;TwoFiles

Sorry about the misunderstanding (which may still be present!)

How are ClientID and ClientCaseID related to one another? Does each
value of ClientID correspond to multiple ClientCaseID's or vice versa?
For that matter, does a Note apply to a particular ClientID, or to a
particular ClientCaseID, or (as I've been assuming) to the combination
of a particular ClientCaseID - TwoField?

Is TwoField filled in for all records, or only where there's a second
child?

Just FWIW, a Primary Key in a table is "a field or a combination of up
to ten fields which uniquely identify a record in the table". If
ClientCaseID 3123, TwoFiles 3 is little Rosie Archuleta, and that
combination of numbers cannot refer to any other person, then you
could use that combination of fields as the Primary Key of the table
of children, since it uniquely identifies one particular child.

John W. Vinson[MVP]

 

[Guest]

1. ClientID is the primary auto number field in my clients table. No
duplicates
2. ClientCaseID# is the number assigned to each record.
In can have duplicates as when there are 2 children. The law firm's
bookkeeping system is set up that way.
For example, Jane Dicken's ClientCaseID#is 2222 and
the 2nd child's retaining file is ClientCaseID#2222 "a" [I changed this to
"2" yesterday so the data in both fields would be the same so in the database
it would be ClientCaseID# 2222 Twofiles 2]. The same parent is billed
separately for each of his/her 2 children. That is why I setup the database
to correspond to the retaining (billing) files.
3. ClientID and ClientCaseID# are in the same table but unrelated. [At your
suggestion I added the ClientID as a primary key]. I also put them in the
Notes table in case I need to link them to the cases form.
4. Each Client ID correspondence to each record. When there are 2 children
there are two records with the same ClientCaseID# and different ClientID.
5. You are correct: the NoteID is linked (applies) to the combination of
ClientCaseID# and TwoFiles. THIS COULD BE THE PROBLEM, MAYBE?
When I link it with the ClientCaseID# only then when there are 2 records
with the same ClientCaseID# the status Notes are entered in each record. When
I link to ClientCaseID and Two fields, then only the records with the "2"
shows any Notes and the other record will not allow any notes to be saved.
6. The TwoField is only filled in when there are 2 children.
Hope these answers will help you help me.
Saw your name in Acknowledgments of John Viescas's book.
Joan

The subform needs to be connected to the record

1. The Master and Child fields match.
2. TwoFiles and ClientCaseID# are both numbers.
3. ClientCaseID# and TwoFiles fields are in both the Cases and Notes tables.
4. ClientID [not to be confused with the ClientCaseID# field] is the
primary key in cases table; NotesID is the primary key in the Notes table.
I am unsure what a two-field Prmary key is.
I noticed the following when I looked at the Notes subform in cases form:
ClientCaseID#2224: there were no notes [1st record in cases]
#2224 "2" [the second record in cases] there were notes
entered.
Thanks for your patience. You usually are able to get thru to me even though
it does take some doing. I really apprecate your assistance.
Joan

IT sounds like the Master/Child Link Field should be

ClientCaseID;TwoFiles

Sorry about the misunderstanding (which may still be present!)

How are ClientID and ClientCaseID related to one another? Does each
value of ClientID correspond to multiple ClientCaseID's or vice versa?
For that matter, does a Note apply to a particular ClientID, or to a
particular ClientCaseID, or (as I've been assuming) to the combination
of a particular ClientCaseID - TwoField?

Is TwoField filled in for all records, or only where there's a second
child?

Just FWIW, a Primary Key in a table is "a field or a combination of up
to ten fields which uniquely identify a record in the table". If
ClientCaseID 3123, TwoFiles 3 is little Rosie Archuleta, and that
combination of numbers cannot refer to any other person, then you
could use that combination of fields as the Primary Key of the table
of children, since it uniquely identifies one particular child.

John W. Vinson[MVP]

 

[John Vinson]

1. ClientID is the primary auto number field in my clients table. No
duplicates
2. ClientCaseID# is the number assigned to each record.

So there are multiple records for each ClientID? You say "each record"
- each record in the Clients table? I'm still not following.

In can have duplicates as when there are 2 children. The law firm's
bookkeeping system is set up that way.
For example, Jane Dicken's ClientCaseID#is 2222 and
the 2nd child's retaining file is ClientCaseID#2222 "a" [I changed this to
"2" yesterday so the data in both fields would be the same so in the database
it would be ClientCaseID# 2222 Twofiles 2]. The same parent is billed
separately for each of his/her 2 children. That is why I setup the database
to correspond to the retaining (billing) files.

Again...

It was *NOT* necessary to change the datatype. The "Twofiles" field
could be numeric, or it could be text; since the paper files have it
text I would suggest using text and leaving it as a or b. I certainly
did not make any suggestion that you should change it to a number, and
I'm not sure what benefit you were seeking when you did.

I presume that for the first child it's ClientCaseID 2222, and
Twofiles is blank or empty?

3. ClientID and ClientCaseID# are in the same table but unrelated. [At your
suggestion I added the ClientID as a primary key]. I also put them in the
Notes table in case I need to link them to the cases form.

If ClientID is the Primary Key then I presume that you'll have records
like

ClientID 412; ClientCaseID 2222; TwoFields <null>
ClientID 510; ClientCaseID 2222; TwoFields 2 <second child>
ClientID 984; ClientCaseID 2222; TwoFields 3 <third child>

Is that correct?

If so, then my suggestion of adding a ClientID may have been unwise.
If each record pertains to an individual child then a two-field
Primary Key - ClientCaseID and TwoFields - may be a more appropriate
primary key. Is ClientCaseID what gets printed on the file folder
label in the filing cabinet?

4. Each Client ID correspondence to each record. When there are 2 children
there are two records with the same ClientCaseID# and different ClientID.
5. You are correct: the NoteID is linked (applies) to the combination of
ClientCaseID# and TwoFiles. THIS COULD BE THE PROBLEM, MAYBE?
When I link it with the ClientCaseID# only then when there are 2 records
with the same ClientCaseID# the status Notes are entered in each record. When
I link to ClientCaseID and Two fields, then only the records with the "2"
shows any Notes and the other record will not allow any notes to be saved.

This is probably because having a NULL value in the Master Link Field
is preventing a link from being created.

6. The TwoField is only filled in when there are 2 children.
Hope these answers will help you help me.

I think there are two possible solutions here:

1. Use ClientID as the Primary Key (a surrogate key it's called) for
the Cases table; and use it as the Master Link Field (rather than
using ClientCaseID, which isn't unique). The foreign key in the Notes
table would then point to an individual record in Cases (linked to the
ClientID) rather than to a whole group of records (all the children
for a client). You'ld need a ClientID field (and NO ClientCaseID and
no ChildNo) in the notes table.

2. Make TwoFields a Required field, default 1, so that the first child
will have 1, the second child 2, the third child 3. You might even
want to change the name of the field to something more meaninful, such
as ChildNo. Make these two fields (ClientCaseID and ChildNo) the
Primary Key, and use them as the Master/Child Link Field. The Notes
table would then need both ClientCaseID and ChildNo fields for the
link.

It's sort of up to you which to use - AFAIK both would work.

John W. Vinson[MVP]

 

[Guest]

I'm doing something wrong.
1. No multiple records for each ClientID.
ClientID is an unique primary key auto numerial field with no duplicates.
Each record in Clients Table has it's own ClientID field.
2. ClientCase#
3. I changed the TwoFiles field back to a text field.
4. Your presumed correctly: for the first child its ClientCaseID# 2222, and
theTwoFiles is blank or empty.
5. Correct:
ClientID 412: ClientCaseID# 2222;TwoFields: empty;NotesID: 214
ClientID 510: ClientCaseID# 2222;TwoFields: 2; NotesID: 214
Problem: I noticed that when I run a qry the Notes ID that should be
different from one another are not.
This may give you a clue.
I have tried so many combinations that need to stop for now.
When I tried your suggestions it didn't work so I am doing something wrong.
Joan

1. ClientID is the primary auto number field in my clients table. No
duplicates
2. ClientCaseID# is the number assigned to each record.

So there are multiple records for each ClientID? You say "each record"
- each record in the Clients table? I'm still not following.

In can have duplicates as when there are 2 children. The law firm's
bookkeeping system is set up that way.
For example, Jane Dicken's ClientCaseID#is 2222 and
the 2nd child's retaining file is ClientCaseID#2222 "a" [I changed this to
"2" yesterday so the data in both fields would be the same so in the database
it would be ClientCaseID# 2222 Twofiles 2]. The same parent is billed
separately for each of his/her 2 children. That is why I setup the database
to correspond to the retaining (billing) files.

Again...

It was *NOT* necessary to change the datatype. The "Twofiles" field
could be numeric, or it could be text; since the paper files have it
text I would suggest using text and leaving it as a or b. I certainly
did not make any suggestion that you should change it to a number, and
I'm not sure what benefit you were seeking when you did.

I presume that for the first child it's ClientCaseID 2222, and
Twofiles is blank or empty?

3. ClientID and ClientCaseID# are in the same table but unrelated. [At your
suggestion I added the ClientID as a primary key]. I also put them in the
Notes table in case I need to link them to the cases form.

If ClientID is the Primary Key then I presume that you'll have records
like

ClientID 412; ClientCaseID 2222; TwoFields <null>
ClientID 510; ClientCaseID 2222; TwoFields 2 <second child>
ClientID 984; ClientCaseID 2222; TwoFields 3 <third child>

Is that correct?

If so, then my suggestion of adding a ClientID may have been unwise.
If each record pertains to an individual child then a two-field
Primary Key - ClientCaseID and TwoFields - may be a more appropriate
primary key. Is ClientCaseID what gets printed on the file folder
label in the filing cabinet?

4. Each Client ID correspondence to each record. When there are 2 children
there are two records with the same ClientCaseID# and different ClientID.
5. You are correct: the NoteID is linked (applies) to the combination of
ClientCaseID# and TwoFiles. THIS COULD BE THE PROBLEM, MAYBE?
When I link it with the ClientCaseID# only then when there are 2 records
with the same ClientCaseID# the status Notes are entered in each record. When
I link to ClientCaseID and Two fields, then only the records with the "2"
shows any Notes and the other record will not allow any notes to be saved.

This is probably because having a NULL value in the Master Link Field
is preventing a link from being created.

6. The TwoField is only filled in when there are 2 children.
Hope these answers will help you help me.

I think there are two possible solutions here:

1. Use ClientID as the Primary Key (a surrogate key it's called) for
the Cases table; and use it as the Master Link Field (rather than
using ClientCaseID, which isn't unique). The foreign key in the Notes
table would then point to an individual record in Cases (linked to the
ClientID) rather than to a whole group of records (all the children
for a client). You'ld need a ClientID field (and NO ClientCaseID and
no ChildNo) in the notes table.

2. Make TwoFields a Required field, default 1, so that the first child
will have 1, the second child 2, the third child 3. You might even
want to change the name of the field to something more meaninful, such
as ChildNo. Make these two fields (ClientCaseID and ChildNo) the
Primary Key, and use them as the Master/Child Link Field. The Notes
table would then need both ClientCaseID and ChildNo fields for the
link.

It's sort of up to you which to use - AFAIK both would work.

John W. Vinson[MVP]

 

[John Vinson]

I'm doing something wrong.

Which of my two suggested approaches were you using - linking by
ClientID or linking by the two fields?

1. No multiple records for each ClientID.

Given that, I am really thinking that you should link Notes by
ClientID and that the Notes table should not contain the ClientCaseID
or TwoFields.

ClientID is an unique primary key auto numerial field with no duplicates.
Each record in Clients Table has it's own ClientID field.
ok...
2. ClientCase#
....???

3. I changed the TwoFiles field back to a text field.
4. Your presumed correctly: for the first child its ClientCaseID# 2222, and
theTwoFiles is blank or empty.
5. Correct:
ClientID 412: ClientCaseID# 2222;TwoFields: empty;NotesID: 214
ClientID 510: ClientCaseID# 2222;TwoFields: 2; NotesID: 214

Problem: I noticed that when I run a qry the Notes ID that should be
different from one another are not.

And this is what I don't understand. YOu "run a qry" - what query?
Could you post the SQL of the query that's not working? or do you mean
you're running a search on your Form? You say "the Notes ID that
should be different from one another are not" - example?

This may give you a clue.
I have tried so many combinations that need to stop for now.
When I tried your suggestions it didn't work so I am doing something wrong.
Joan

Well... since I don't know *specifically* what you tried, then I have
no way to know what you did wrong.

Let's go back to basics: this will be repeating some stuff.

What is (now) the Primary Key of Cases?
What is the Relationship between Cases and Notes? Which field or
fields in Cases is linked to which field or fields in Notes?
What is the Recordsource of the main form (if it's something other
than just the Cases table, please post the SQL).
What is the Recordsource of the subform (if it's something other than
just the Notes table, please post the SQL).
What is the Master Link Field property of the subform? Please copy and
paste it here.
Ditto the Child Link Field.
What data are you *SEEING* in the Subform that is incorrect?

We'll whup this monster yet! Thanks for sticking with it...

John W. Vinson[MVP]

 

[Guest]

Which of my two suggested approaches were you using - linking by
ClientID or linking by the two fields?
Ans: I tried both without success.
ClientID and that the Notes table should not contain the ClientCaseID
or TwoFields.
Ans. ok
Ans. Should be *ClientCaseID#* that is assisgned to each record. Duplicates -Yes_ because of the neccessity of adding a second child
....???



And this is what I don't understand. YOu "run a qry" - what query?
Could you post the SQL of the query that's not working? or do you mean
you're running a search on your Form?

Ans. I ran the query from Notes Form whose Property is Notes Table.
SQL: SELECT NotesTable.[Notes Text], NotesTable.[NotesID#],
NotesTable.[ClientCaseID#], NotesTable.TwoFiles
FROM NotesTable;

You say "the Notes ID that should be different from one another are not" - example?
Ans. I misspoke. The *NotesID#* does change with each entry of a record in the subform, BUT is duplicated in the record with the TwoFields. *NotesID#* is the primary auto number w/out duplicates of the Notes Table.

Well... since I don't know *specifically* what you tried, then I have
no way to know what you did wrong.

Let's go back to basics: this will be repeating some stuff.

What is (now) the Primary Key of Cases? Ans: ClientID
What is the Relationship between Cases and Notes?

Ans. Cases and Notes are jointed in my *qryNotesTableAll*: Notes Table and
Main Client Query joined by CaseClientID#. "only include rows where the
joined field from both tables are equal".
Sql:SELECT [Main Query Clients].ClientID, NotesTable.[NotesID#],
NotesTable.[Notes Text], NotesTable.[ClientCaseID#], [Main Query
Clients].[LastName Father], [Main Query Clients].[FirstName Father], [Main
Query Clients].[FirstName Mother], [Main Query Clients].[Case Handler], [Main
Query Clients].[Case Handler2], [Main Query Clients].[Case Opened or Closed]
FROM NotesTable INNER JOIN [Main Query Clients] ON
NotesTable.[ClientCaseID#] = [Main Query Clients].[ClientCaseID#]
WHERE ((([Main Query Clients].[Case Handler])=[Enter Your Last Name]) AND
(([Main Query Clients].[Case Opened or Closed])="yes"));

Which field or fields in Cases is linked to which field or fields in Notes?
Ans. ClientCaseID#

What is the Recordsource of the main form (if it's something other
than just the Cases table, please post the SQL).

Ans. It is the Main Client Query
SELECT Clients.[NotesID#], Clients.ClientID, Clients.[ClientCaseID#],
Clients.Code_No, Clients.Schcode, Clients.SchDisCon, Clients.SchoolCode,
Clients.CodeDefFirm, Clients.Defcslid, Clients.PrefixFather,
Clients.[FirstName Father], Clients.[LastName Father], Clients.PrefixMother,
Clients.[FirstName Mother], Clients.[LastName Mother], Clients.[Child First
Name], Clients.[Child Last Name], Clients.Address, Clients.Address1,
Clients.City, Clients.State, Clients.PostalCode, Clients.Email, Clients.[Home
Phone], Clients.WorkPhone, Clients.WorkExtension, Clients.WPhone,
Clients.WPhone1, Clients.WorkPhone2, Clients.WorkPhone3, Clients.MobilePhone,
Clients.CellPhone, Clients.FaxNumber, Clients.EmailName, Clients.ReferredBy,
Clients.[Type of Case], Clients.[Date Opened], Clients.[Date Closed],
Clients.[Case Handler], Clients.[Case Handler2], Clients.[Case Opened or
Closed], Clients.[File Out], Clients.Notes, Clients.Notes1,
Clients.RequestedRecords1, Clients.RequestedRecords, Clients.RecRecords,
Clients.[Recd Records1], Clients.[File Reviewed], Clients.RetainerLetter,
Clients.[Amount Retainer], Clients.[Amount Recevied], Clients.[Amount Used],
Clients.RequestHearing, Clients.HearingDate, Clients.RequestMed,
Clients.MedDate, Clients.Advisory, Clients.[PPT Date], Clients.SettleAgrmt,
Clients.SettleAgrmt1, Clients.SettleReached, Clients.Settlefunds,
Clients.Settlefunds1, Clients.RetainerRcdDate, Clients.Twofiles,
Clients.PrintFlag, Clients.Pulled, Clients.AttendMed, Clients.Advisory,
Clients.TimeMed, Clients.AttendDPH, Clients.TimeDPH, Clients.AttendPPT,
Clients.TimePPT, Clients.Probono, Clients.[Closed Letter], Clients.[Hours
Billed], Clients.[Amount Received], [School District].*, [District
Contacts].*, Schools.*, [Defense Firm].*, [Defense Attorney].*, Status.*,
Clients.RecRecords
FROM Status RIGHT JOIN ([Defense Firm] RIGHT JOIN ([Defense Attorney] RIGHT
JOIN (((Clients LEFT JOIN [District Contacts] ON Clients.SchDisCon =
[District Contacts].SchDisCon) LEFT JOIN [School District] ON Clients.Schcode
= [School District].Schcode) LEFT JOIN Schools ON Clients.SchoolCode =
Schools.SchoolCode) ON [Defense Attorney].Defcslid = Clients.Defcslid) ON
[Defense Firm].CodeDefFirm = Clients.CodeDefFirm) ON Status.Code_No =
Clients.Code_No;

What is the Recordsource of the subform (if it's something other than
just the Notes table, please post the SQL).

Ans. SELECT NotesTable.[Notes Text], NotesTable.[NotesID#],
NotesTable.[ClientCaseID#], NotesTable.TwoFiles
FROM NotesTable;

 

[John Vinson]

On Sun, 20 Mar 2005 11:07:02 -0800, JoanOC

Answers inline.

And this is what I don't understand. YOu "run a qry" - what query?
Could you post the SQL of the query that's not working? or do you mean
you're running a search on your Form?

Ans. I ran the query from Notes Form whose Property is Notes Table.
SQL: SELECT NotesTable.[Notes Text], NotesTable.[NotesID#],
NotesTable.[ClientCaseID#], NotesTable.TwoFiles
FROM NotesTable;

You say "the Notes ID that should be different from one another are not" - example?
Ans. I misspoke. The *NotesID#* does change with each entry of a record in the subform, BUT is duplicated in the record with the TwoFields. *NotesID#* is the primary auto number w/out duplicates of the Notes Table.

I do not understand that sentence. "is duplcated in the record with
the TwoFields" - could you post SOME DATA? an example of what you're
seeing and what you would expect to see?

Ans. Cases and Notes are jointed in my *qryNotesTableAll*: Notes Table and

That's NOT WHAT I ASKED.

If you open the Relationships window is there a Relationship defined
between the Cases table and the Notes table?

If so, what field or fields define the relationship?

If not... *define the relationship*. It's necessary to maintain
integrity of your data!

Main Client Query joined by CaseClientID#. "only include rows where the
joined field from both tables are equal".
Sql:SELECT [Main Query Clients].ClientID, NotesTable.[NotesID#],
NotesTable.[Notes Text], NotesTable.[ClientCaseID#], [Main Query
Clients].[LastName Father], [Main Query Clients].[FirstName Father], [Main
Query Clients].[FirstName Mother], [Main Query Clients].[Case Handler], [Main
Query Clients].[Case Handler2], [Main Query Clients].[Case Opened or Closed]
FROM NotesTable INNER JOIN [Main Query Clients] ON
NotesTable.[ClientCaseID#] = [Main Query Clients].[ClientCaseID#]

Ok... so you're joining the tables by ClientCaseID#. The value of
TeoFields is not used; for each record in [Main Query Clients] you'll
see all records with a matching ClientCaseID#, and the value of
TwoFields will not be used to make the link and will be ignored.

I suspect that this is not what you want. However, that's what you're
asking for.

WHERE ((([Main Query Clients].[Case Handler])=[Enter Your Last Name]) AND
(([Main Query Clients].[Case Opened or Closed])="yes"));

Which field or fields in Cases is linked to which field or fields in Notes?
Ans. ClientCaseID#

What is the Recordsource of the main form (if it's something other
than just the Cases table, please post the SQL).

Ans. It is the Main Client Query
SELECT Clients.[NotesID#], Clients.ClientID, Clients.[ClientCaseID#],
Clients.Code_No, Clients.Schcode, Clients.SchDisCon, Clients.SchoolCode, ...

What is the Recordsource of the subform (if it's something other than
just the Notes table, please post the SQL).

Ans. SELECT NotesTable.[Notes Text], NotesTable.[NotesID#],
NotesTable.[ClientCaseID#], NotesTable.TwoFiles
FROM NotesTable;

What is the Master Link Field property of the subform? Please copy and
paste it here. Ans. ClientCaseID#
Ditto the Child Link Field.

Ans. Client CaseID#

Ok.

I've suggested - several times - that you CHANGE THIS.

You have not changed it.

Please try this:

Change the Master Link Field to

[ClientCaseID#];[TwoFiles]

and the Child Link Field to

[ClientCaseID#];[TwoFiles]

You may also need to fill in a non-NULL value in EVERY RECORD - first
child, second child, whatever - in both tables, since you cannot join
on a NULL. If TwoFiles is now a number, I'd suggest 1 for the first
child, 2 for the second, and so on. But leaving it empty *will* cause
you problems!

John W. Vinson[MVP]

 

[Guest]

1.Below is sample of what I mean by duplication of Notes entries:
ClientCaseID#: 2222
Child Name: Mary
Notes SubForm Entry: 3/17/05: The quick brown fox jumped.
ClientCaseID#: 2222 TwoFiles 2
Child Name: Joe
Notes SubForm Entry: 3/17/05: The quick brown fox jumped.
The notes for Joe were automatically entered or duplicated when the attorney
entered his notes for Mary.
I'm getting so confused.
Isn't there any easier way? What about creating a sub-sub form for the 2nd
child.
In Viescas's book there was something about doing that ealing with Complex
Subforms.pg 79 if you have his book handy.
I'll keep trying to apply your suggestions re the 2 solutions of Sat 3/18.
Also, will dig deeper into what you sent today.
Thanks for your help. Joan

2. I dont' know how to send you the Relationships of Cases.
It was a mess.
I added the Notes Table and defined the relationship between Notes and Cases
by joining the the NotesID#.
3. Re: Master and Child Links:I have tried a zillion times to do as you
instructed, but when I do the Notes Subform on my Main form the notes are
empty. When I double check the Notes Form itself the notes are there.
I'll keep working at it, but

On Sun, 20 Mar 2005 11:07:02 -0800, JoanOC

Answers inline.

And this is what I don't understand. YOu "run a qry" - what query?
Could you post the SQL of the query that's not working? or do you mean
you're running a search on your Form?

Ans. I ran the query from Notes Form whose Property is Notes Table.
SQL: SELECT NotesTable.[Notes Text], NotesTable.[NotesID#],
NotesTable.[ClientCaseID#], NotesTable.TwoFiles
FROM NotesTable;

You say "the Notes ID that should be different from one another are not" - example?
Ans. I misspoke. The *NotesID#* does change with each entry of a record in the subform, BUT is duplicated in the record with the TwoFields. *NotesID#* is the primary auto number w/out duplicates of the Notes Table.

I do not understand that sentence. "is duplcated in the record with
the TwoFields" - could you post SOME DATA? an example of what you're
seeing and what you would expect to see?

Ans. Cases and Notes are jointed in my *qryNotesTableAll*: Notes Table and

That's NOT WHAT I ASKED.

If you open the Relationships window is there a Relationship defined
between the Cases table and the Notes table?

If so, what field or fields define the relationship?

If not... *define the relationship*. It's necessary to maintain
integrity of your data!

Main Client Query joined by CaseClientID#. "only include rows where the
joined field from both tables are equal".
Sql:SELECT [Main Query Clients].ClientID, NotesTable.[NotesID#],
NotesTable.[Notes Text], NotesTable.[ClientCaseID#], [Main Query
Clients].[LastName Father], [Main Query Clients].[FirstName Father], [Main
Query Clients].[FirstName Mother], [Main Query Clients].[Case Handler], [Main
Query Clients].[Case Handler2], [Main Query Clients].[Case Opened or Closed]
FROM NotesTable INNER JOIN [Main Query Clients] ON
NotesTable.[ClientCaseID#] = [Main Query Clients].[ClientCaseID#]

Ok... so you're joining the tables by ClientCaseID#. The value of
TeoFields is not used; for each record in [Main Query Clients] you'll
see all records with a matching ClientCaseID#, and the value of
TwoFields will not be used to make the link and will be ignored.

I suspect that this is not what you want. However, that's what you're
asking for.

WHERE ((([Main Query Clients].[Case Handler])=[Enter Your Last Name]) AND
(([Main Query Clients].[Case Opened or Closed])="yes"));

Which field or fields in Cases is linked to which field or fields in Notes?
Ans. ClientCaseID#

What is the Recordsource of the main form (if it's something other
than just the Cases table, please post the SQL).

Ans. It is the Main Client Query
SELECT Clients.[NotesID#], Clients.ClientID, Clients.[ClientCaseID#],
Clients.Code_No, Clients.Schcode, Clients.SchDisCon, Clients.SchoolCode, ...

What is the Recordsource of the subform (if it's something other than
just the Notes table, please post the SQL).

Ans. SELECT NotesTable.[Notes Text], NotesTable.[NotesID#],
NotesTable.[ClientCaseID#], NotesTable.TwoFiles
FROM NotesTable;

What is the Master Link Field property of the subform? Please copy and
paste it here. Ans. ClientCaseID#
Ditto the Child Link Field.

Ans. Client CaseID#

Ok.

I've suggested - several times - that you CHANGE THIS.

You have not changed it.

Please try this:

Change the Master Link Field to

[ClientCaseID#];[TwoFiles]

and the Child Link Field to

[ClientCaseID#];[TwoFiles]

You may also need to fill in a non-NULL value in EVERY RECORD - first
child, second child, whatever - in both tables, since you cannot join
on a NULL. If TwoFiles is now a number, I'd suggest 1 for the first
child, 2 for the second, and so on. But leaving it empty *will* cause
you problems!

John W. Vinson[MVP]

 

[John Vinson]

1.Below is sample of what I mean by duplication of Notes entries:
ClientCaseID#: 2222
Child Name: Mary
Notes SubForm Entry: 3/17/05: The quick brown fox jumped.
ClientCaseID#: 2222 TwoFiles 2
Child Name: Joe
Notes SubForm Entry: 3/17/05: The quick brown fox jumped.
The notes for Joe were automatically entered or duplicated when the attorney
entered his notes for Mary.
I'm getting so confused.

The reason you are seeing the same entry for both records is that
THAT IS PRECISELY AND EXACTLY what you are Asking Access to give you.

Your master/child link field is ClientCaseID#.

Therefore, if the Mainform has ClientCaseID# 2222, you will see the
notes for ClientCaseID# 2222.

That's what the link field is DESIGNED TO DO.

Isn't there any easier way? What about creating a sub-sub form for the 2nd
child.

And an eighth subform for the eighth child? That way lies madness!

In Viescas's book there was something about doing that ealing with Complex
Subforms.pg 79 if you have his book handy.

This is a SIMPLE SUBFORM.

I'll keep trying to apply your suggestions re the 2 solutions of Sat 3/18.
Also, will dig deeper into what you sent today.
Thanks for your help. Joan

2. I dont' know how to send you the Relationships of Cases.
It was a mess.
I added the Notes Table and defined the relationship between Notes and Cases
by joining the the NotesID#.

WRONG!!!!

I'm sorry to yell, but you're *not understanding how relationships
work*!

In a One (case) to Many (notes) relationship, you should have a field
in the One table - its Primary Key.

This should be linked to a field in the Many table, *not* that table's
primary key; this field is called a Foreign Key.

I would suggest (if you don't have lots of data already entered) that
you DELETE your Notes table and recreate it.

Its Primary Key should be NotesID. (I'd recommend not using the #
character in fieldnames).

It should have a Long Integer field CaseID.

You should have a relationship from the CaseID field in Cases, the
Primary Key,to the CaseID field in Notes.

This field should also be the master/child link field in the Form.

The Notes table should NOT contain the ClientCase# field or the
TwoFiles field. These will exist in the Cases table though.


John W. Vinson[MVP]

 

[Guest]

If you have given up on this I will more than understand. I am almost at that
point myself.
Stumbling blocks: It took me a while to learn how to create 2 primary keys
in a table.
I am now trying to do as you instructed 3/19: 2nd option Make Twofields a
Required field, default 1, etc.
When I attempt to make Twofields and ClientCaseID# in their tables a
required field I keep getting an error message about duplicates and indexes
and to change data..
Question: In the tables of Notes and Cases could you please tell me what the
index and required entries should be? I have tried every combination without
success.
In Cases table I made TwoField and Client CaseID# Primary keys.
I made TwoFields a Required field and default 1, withYes dups ok and tried
dups No
I understand that both TwoFields and ClientCaseID# need to be linked.
I know that ClientCaseID# has to allow duplicates.
I thought TwoField should not allow duplicates.
I also have ordered Access Inside Out 2003 just in case you have given up on
this -
Joan

1.Below is sample of what I mean by duplication of Notes entries:
ClientCaseID#: 2222
Child Name: Mary
Notes SubForm Entry: 3/17/05: The quick brown fox jumped.
ClientCaseID#: 2222 TwoFiles 2
Child Name: Joe
Notes SubForm Entry: 3/17/05: The quick brown fox jumped.
The notes for Joe were automatically entered or duplicated when the attorney
entered his notes for Mary.
I'm getting so confused.
Isn't there any easier way? What about creating a sub-sub form for the 2nd
child.
In Viescas's book there was something about doing that ealing with Complex
Subforms.pg 79 if you have his book handy.
I'll keep trying to apply your suggestions re the 2 solutions of Sat 3/18.
Also, will dig deeper into what you sent today.
Thanks for your help. Joan

2. I dont' know how to send you the Relationships of Cases.
It was a mess.
I added the Notes Table and defined the relationship between Notes and Cases
by joining the the NotesID#.
3. Re: Master and Child Links:I have tried a zillion times to do as you
instructed, but when I do the Notes Subform on my Main form the notes are
empty. When I double check the Notes Form itself the notes are there.
I'll keep working at it, but

On Sun, 20 Mar 2005 11:07:02 -0800, JoanOC

Answers inline.

And this is what I don't understand. YOu "run a qry" - what query?
Could you post the SQL of the query that's not working? or do you mean
you're running a search on your Form?
Ans. I ran the query from Notes Form whose Property is Notes Table.
SQL: SELECT NotesTable.[Notes Text], NotesTable.[NotesID#],
NotesTable.[ClientCaseID#], NotesTable.TwoFiles
FROM NotesTable;

You say "the Notes ID that should be different from one another are not" - example?
Ans. I misspoke. The *NotesID#* does change with each entry of a record in the subform, BUT is duplicated in the record with the TwoFields. *NotesID#* is the primary auto number w/out duplicates of the Notes Table.

I do not understand that sentence. "is duplcated in the record with
the TwoFields" - could you post SOME DATA? an example of what you're
seeing and what you would expect to see?

What is (now) the Primary Key of Cases?
Ans: ClientID
What is the Relationship between Cases and Notes?
Ans. Cases and Notes are jointed in my *qryNotesTableAll*: Notes Table and

That's NOT WHAT I ASKED.

If you open the Relationships window is there a Relationship defined
between the Cases table and the Notes table?

If so, what field or fields define the relationship?

If not... *define the relationship*. It's necessary to maintain
integrity of your data!

Main Client Query joined by CaseClientID#. "only include rows where the
joined field from both tables are equal".
Sql:SELECT [Main Query Clients].ClientID, NotesTable.[NotesID#],
NotesTable.[Notes Text], NotesTable.[ClientCaseID#], [Main Query
Clients].[LastName Father], [Main Query Clients].[FirstName Father], [Main
Query Clients].[FirstName Mother], [Main Query Clients].[Case Handler], [Main
Query Clients].[Case Handler2], [Main Query Clients].[Case Opened or Closed]
FROM NotesTable INNER JOIN [Main Query Clients] ON
NotesTable.[ClientCaseID#] = [Main Query Clients].[ClientCaseID#]

Ok... so you're joining the tables by ClientCaseID#. The value of
TeoFields is not used; for each record in [Main Query Clients] you'll
see all records with a matching ClientCaseID#, and the value of
TwoFields will not be used to make the link and will be ignored.

I suspect that this is not what you want. However, that's what you're
asking for.

WHERE ((([Main Query Clients].[Case Handler])=[Enter Your Last Name]) AND
(([Main Query Clients].[Case Opened or Closed])="yes"));

Which field or fields in Cases is linked to which field or fields in Notes?
Ans. ClientCaseID#
What is the Recordsource of the main form (if it's something other
than just the Cases table, please post the SQL).
Ans. It is the Main Client Query
SELECT Clients.[NotesID#], Clients.ClientID, Clients.[ClientCaseID#],
Clients.Code_No, Clients.Schcode, Clients.SchDisCon, Clients.SchoolCode, ...

What is the Recordsource of the subform (if it's something other than
just the Notes table, please post the SQL).
Ans. SELECT NotesTable.[Notes Text], NotesTable.[NotesID#],
NotesTable.[ClientCaseID#], NotesTable.TwoFiles
FROM NotesTable;

What is the Master Link Field property of the subform? Please copy and
paste it here.
Ans. ClientCaseID#
Ditto the Child Link Field.
Ans. Client CaseID#

Ok.

I've suggested - several times - that you CHANGE THIS.

You have not changed it.

Please try this:

Change the Master Link Field to

[ClientCaseID#];[TwoFiles]

and the Child Link Field to

[ClientCaseID#];[TwoFiles]

You may also need to fill in a non-NULL value in EVERY RECORD - first
child, second child, whatever - in both tables, since you cannot join
on a NULL. If TwoFiles is now a number, I'd suggest 1 for the first
child, 2 for the second, and so on. But leaving it empty *will* cause
you problems!

John W. Vinson[MVP]

 

[John Vinson]

If you have given up on this I will more than understand. I am almost at that
point myself.

:-{( I was out of town for a few days but We Can Solve This!

Stumbling blocks: It took me a while to learn how to create 2 primary keys
in a table.

Nitpick, but an important jargon term: You cannot have two primary
keys in a table, but only *one*. However, that Primary Key can consist
of one field, or of two, or of ten fields.

I am now trying to do as you instructed 3/19: 2nd option Make Twofields a
Required field, default 1, etc.
When I attempt to make Twofields and ClientCaseID# in their tables a
required field I keep getting an error message about duplicates and indexes
and to change data..

You'll need to run an Update query in both tables; use a criterion of
Is Null on Twofields, to select only the first child in each case; and
on the Update To row put 1. This will then allow you to make it a
required field.

The error message about duplicates is almost certainly coming not from
making the field required, but with making it the Primary Key.

Question: In the tables of Notes and Cases could you please tell me what the
index and required entries should be? I have tried every combination without
success.

You'll need to:

- run the update query above so that there are no records with NULL
value of TwoFields
- Make the combination of ClientCaseID# and TwoFields the joint
Primary Key of Cases
- Don't create ANY new indexes in Notes

In Cases table I made TwoField and Client CaseID# Primary keys.
I made TwoFields a Required field and default 1, withYes dups ok and tried
dups No

Don't set a default for TwoFields in the Notes table; it will get
filled in to match the corresponding record in Cases, using the
master/child link field of the subform. You don't want Access putting
a 1 in there because it might be a record for a different child.

There should NOT be a separate index on TwoFields. It's not needed.

I understand that both TwoFields and ClientCaseID# need to be linked.
I know that ClientCaseID# has to allow duplicates.
I thought TwoField should not allow duplicates.

Ummm... that would mean that once you had one record in the table with
1 in the Twofields field, then you could never add another record with
1 in the Twofields field. That's not what you want. Again... you don't
need ANY index on Twofields. Creating a Primary Key on the combination
of ClientCaseID# and TwoFields *DOES* create an index for you,
automatically; that's the only index you need. And when you use the
relationships window to join Cases.ClientCaseID# to
Notes.ClientCaseID#, and join Cases.Twofields to Notes.Twofields, and
check the Enforce Referential Integrity checkbox, Access will create
an index on the two fields in the Notes table. You don't need to do
ANYTHING else.

I also have ordered Access Inside Out 2003 just in case you have given up on
this -
Joan

It'll be a good investment regardless!

John W. Vinson[MVP]

 

[Guest]

Finally succeeded in getting the Subform link to work. Thank you for all you
help.
Joan

1.Below is sample of what I mean by duplication of Notes entries:
ClientCaseID#: 2222
Child Name: Mary
Notes SubForm Entry: 3/17/05: The quick brown fox jumped.
ClientCaseID#: 2222 TwoFiles 2
Child Name: Joe
Notes SubForm Entry: 3/17/05: The quick brown fox jumped.
The notes for Joe were automatically entered or duplicated when the attorney
entered his notes for Mary.
I'm getting so confused.
Isn't there any easier way? What about creating a sub-sub form for the 2nd
child.
In Viescas's book there was something about doing that ealing with Complex
Subforms.pg 79 if you have his book handy.
I'll keep trying to apply your suggestions re the 2 solutions of Sat 3/18.
Also, will dig deeper into what you sent today.
Thanks for your help. Joan

2. I dont' know how to send you the Relationships of Cases.
It was a mess.
I added the Notes Table and defined the relationship between Notes and Cases
by joining the the NotesID#.
3. Re: Master and Child Links:I have tried a zillion times to do as you
instructed, but when I do the Notes Subform on my Main form the notes are
empty. When I double check the Notes Form itself the notes are there.
I'll keep working at it, but

On Sun, 20 Mar 2005 11:07:02 -0800, JoanOC

Answers inline.

And this is what I don't understand. YOu "run a qry" - what query?
Could you post the SQL of the query that's not working? or do you mean
you're running a search on your Form?
Ans. I ran the query from Notes Form whose Property is Notes Table.
SQL: SELECT NotesTable.[Notes Text], NotesTable.[NotesID#],
NotesTable.[ClientCaseID#], NotesTable.TwoFiles
FROM NotesTable;

You say "the Notes ID that should be different from one another are not" - example?
Ans. I misspoke. The *NotesID#* does change with each entry of a record in the subform, BUT is duplicated in the record with the TwoFields. *NotesID#* is the primary auto number w/out duplicates of the Notes Table.

I do not understand that sentence. "is duplcated in the record with
the TwoFields" - could you post SOME DATA? an example of what you're
seeing and what you would expect to see?

What is (now) the Primary Key of Cases?
Ans: ClientID
What is the Relationship between Cases and Notes?
Ans. Cases and Notes are jointed in my *qryNotesTableAll*: Notes Table and

That's NOT WHAT I ASKED.

If you open the Relationships window is there a Relationship defined
between the Cases table and the Notes table?

If so, what field or fields define the relationship?

If not... *define the relationship*. It's necessary to maintain
integrity of your data!

Main Client Query joined by CaseClientID#. "only include rows where the
joined field from both tables are equal".
Sql:SELECT [Main Query Clients].ClientID, NotesTable.[NotesID#],
NotesTable.[Notes Text], NotesTable.[ClientCaseID#], [Main Query
Clients].[LastName Father], [Main Query Clients].[FirstName Father], [Main
Query Clients].[FirstName Mother], [Main Query Clients].[Case Handler], [Main
Query Clients].[Case Handler2], [Main Query Clients].[Case Opened or Closed]
FROM NotesTable INNER JOIN [Main Query Clients] ON
NotesTable.[ClientCaseID#] = [Main Query Clients].[ClientCaseID#]

Ok... so you're joining the tables by ClientCaseID#. The value of
TeoFields is not used; for each record in [Main Query Clients] you'll
see all records with a matching ClientCaseID#, and the value of
TwoFields will not be used to make the link and will be ignored.

I suspect that this is not what you want. However, that's what you're
asking for.

WHERE ((([Main Query Clients].[Case Handler])=[Enter Your Last Name]) AND
(([Main Query Clients].[Case Opened or Closed])="yes"));

Which field or fields in Cases is linked to which field or fields in Notes?
Ans. ClientCaseID#
What is the Recordsource of the main form (if it's something other
than just the Cases table, please post the SQL).
Ans. It is the Main Client Query
SELECT Clients.[NotesID#], Clients.ClientID, Clients.[ClientCaseID#],
Clients.Code_No, Clients.Schcode, Clients.SchDisCon, Clients.SchoolCode, ...

What is the Recordsource of the subform (if it's something other than
just the Notes table, please post the SQL).
Ans. SELECT NotesTable.[Notes Text], NotesTable.[NotesID#],
NotesTable.[ClientCaseID#], NotesTable.TwoFiles
FROM NotesTable;

What is the Master Link Field property of the subform? Please copy and
paste it here.
Ans. ClientCaseID#
Ditto the Child Link Field.
Ans. Client CaseID#

Ok.

I've suggested - several times - that you CHANGE THIS.

You have not changed it.

Please try this:

Change the Master Link Field to

[ClientCaseID#];[TwoFiles]

and the Child Link Field to

[ClientCaseID#];[TwoFiles]

You may also need to fill in a non-NULL value in EVERY RECORD - first
child, second child, whatever - in both tables, since you cannot join
on a NULL. If TwoFiles is now a number, I'd suggest 1 for the first
child, 2 for the second, and so on. But leaving it empty *will* cause
you problems!

John W. Vinson[MVP]

 

[John Vinson]

Finally succeeded in getting the Subform link to work. Thank you for all you
help.

Great! Glad to have been of assistance.


John W. Vinson[MVP]