Conditionally delete specified text from field

[Geezer via AccessMonster.com]

I think this is possible, but I can't seem to figure out how.

Here is some table structure information:

Surgery
ID (this is Subject ID not the PK)
Breast
Surg_Date
Surg_Type
Surg_Results

Code_breast_side
Code (relates to Surgery.Breast)
Breast_Side

Code_Surgery
Surgery_Code (relates to Surgery.Surg_Type)
Surgery_Code_Description

Code_Surgery_Diagnosis
Code_Surgery_Diagnosis (relates to Surgery.Surg_Results)
Code_Surgery_Diagnosis_Description

FollowupNotes
ID (relates one from here to many in Surgery)
Notes_HealthHX


I linked the above tables and ran an Update query to add comments to
FollowupNotes.Notes_HealthHX in the following format (for certain Surg_Types
only - 2, 3, 4, 5, and 5a):

[Surg_Date] & ", " & [Breast_Side] & ", " & [Surgery_Code_Description] & ", "
& [Code_Surgery_Diagnosis_Description]

The Update query worked well and seemed to add the appropriate comments in
the Notes_HealthHX field. There is now a comment for each specified
Surg_Type. Now, the users want all except one of these added comments
removed according to a specific logic. If Notes_HealthHX has more than one
comment for the same breast then leave the highest priority Surg_Type (the
priority order is 2, then 3, then 4, then 5, and finally 5a). Similarly, if
a Subject has more than one of the same Surg_Types, with different dates,
leave only the record with the most recent date. Below are some examples.

Current Notes_HealthHX:
2/4/2006, Right breast, Needle Biopsy, Ductal Insitu
6/25/2003, Right breast, Mastectomy, Normal

Desired Notes_HealthHX:
6/25/2003, Right breast, Mastectomy, Normal

Mastectomy is Surg_Type=2 so is a higher priority than Needle Biopsy, which
is Surg_Type=5. So I'd like to delete "2/4/2006, Right breast, Needle Biopsy,
Ductal Insitu" from Notes_HealthHX, but leave any other text currently in the
field there.

Current Notes_HealthHX example 2:
9/14/2003, Left breast, Mastectomy, Ductal Insitu
7/26/2002, Left breast, Mastectomy, Normal
4/17/2009, Left breast, Mastectomy, Invasive

Desired Notes_HealthHX example2:
4/17/2009, Left breast, Mastectomy, Invasive

Since there are multiple comments relating to the left breast, I want only
the one with the most recent date to remain.

Notes_HealthHX may contain additional comments in addition to those I added
with the Update query. I don't know how to search for a text string when I
don't know for sure what that string will be; I.E. I don't know the date, the
Surg_Type can be one of five, and the Surg_Results could be any of 17.

I would think the starting point would be to identify Notes_HealthHX records
with multiple strings in the format of the Update query, but how to identify
what gets replaced with "" is stumping me. Is this a Mid(), InStr(), LEN(),
or some combination? Also, since there may be additional text in the field,
I can't replace the entire contents of Notes_HealthHX, I want to just
replace/delete/remove the desired [date], [side], [type], [results] text.

Have I been at all clear about what I'm after? Is this doable? Thanks for
any insight!

 

[KARL DEWEY]

I would reccomend adding a Yes/No field named 'Old' has have criteria in
queries to not pull old comment.
But I got lost in your relationship. Do you have a note per visit in a
different record or are the Notes_HealthHX a memo field and comment
contenated?

I think this is possible, but I can't seem to figure out how.

Here is some table structure information:

Surgery
ID (this is Subject ID not the PK)
Breast
Surg_Date
Surg_Type
Surg_Results

Code_breast_side
Code (relates to Surgery.Breast)
Breast_Side

Code_Surgery
Surgery_Code (relates to Surgery.Surg_Type)
Surgery_Code_Description

Code_Surgery_Diagnosis
Code_Surgery_Diagnosis (relates to Surgery.Surg_Results)
Code_Surgery_Diagnosis_Description

FollowupNotes
ID (relates one from here to many in Surgery)
Notes_HealthHX


I linked the above tables and ran an Update query to add comments to
FollowupNotes.Notes_HealthHX in the following format (for certain Surg_Types
only - 2, 3, 4, 5, and 5a):

[Surg_Date] & ", " & [Breast_Side] & ", " & [Surgery_Code_Description] & ", "
& [Code_Surgery_Diagnosis_Description]

The Update query worked well and seemed to add the appropriate comments in
the Notes_HealthHX field. There is now a comment for each specified
Surg_Type. Now, the users want all except one of these added comments
removed according to a specific logic. If Notes_HealthHX has more than one
comment for the same breast then leave the highest priority Surg_Type (the
priority order is 2, then 3, then 4, then 5, and finally 5a). Similarly, if
a Subject has more than one of the same Surg_Types, with different dates,
leave only the record with the most recent date. Below are some examples.

Current Notes_HealthHX:
2/4/2006, Right breast, Needle Biopsy, Ductal Insitu
6/25/2003, Right breast, Mastectomy, Normal

Desired Notes_HealthHX:
6/25/2003, Right breast, Mastectomy, Normal

Mastectomy is Surg_Type=2 so is a higher priority than Needle Biopsy, which
is Surg_Type=5. So I'd like to delete "2/4/2006, Right breast, Needle Biopsy,
Ductal Insitu" from Notes_HealthHX, but leave any other text currently in the
field there.

Current Notes_HealthHX example 2:
9/14/2003, Left breast, Mastectomy, Ductal Insitu
7/26/2002, Left breast, Mastectomy, Normal
4/17/2009, Left breast, Mastectomy, Invasive

Desired Notes_HealthHX example2:
4/17/2009, Left breast, Mastectomy, Invasive

Since there are multiple comments relating to the left breast, I want only
the one with the most recent date to remain.

Notes_HealthHX may contain additional comments in addition to those I added
with the Update query. I don't know how to search for a text string when I
don't know for sure what that string will be; I.E. I don't know the date, the
Surg_Type can be one of five, and the Surg_Results could be any of 17.

I would think the starting point would be to identify Notes_HealthHX records
with multiple strings in the format of the Update query, but how to identify
what gets replaced with "" is stumping me. Is this a Mid(), InStr(), LEN(),
or some combination? Also, since there may be additional text in the field,
I can't replace the entire contents of Notes_HealthHX, I want to just
replace/delete/remove the desired [date], [side], [type], [results] text.

Have I been at all clear about what I'm after? Is this doable? Thanks for
any insight!

 

[Geezer via AccessMonster.com]

Thanks Karl. The Notes_HealthHX is a memo field, there is one record per
subject and additional notes are simply added; it's not a new Notes record
per visit. The addition of Notes is done by concatenating several fields and
putting that into the existing record. I have added code that determines if
a surgery comment already exists in Notes, but I'm not sure if the existing
records, that might already have two, three, four surgery Notes can be
cleaned up automatically or if it will need to be done manually.

I would reccomend adding a Yes/No field named 'Old' has have criteria in
queries to not pull old comment.
But I got lost in your relationship. Do you have a note per visit in a
different record or are the Notes_HealthHX a memo field and comment
contenated?

I think this is possible, but I can't seem to figure out how.

[quoted text clipped - 76 lines]

Have I been at all clear about what I'm after? Is this doable? Thanks for
any insight!

 

[John W. Vinson]

Thanks Karl. The Notes_HealthHX is a memo field, there is one record per
subject and additional notes are simply added; it's not a new Notes record
per visit. The addition of Notes is done by concatenating several fields and
putting that into the existing record. I have added code that determines if
a surgery comment already exists in Notes, but I'm not sure if the existing
records, that might already have two, three, four surgery Notes can be
cleaned up automatically or if it will need to be done manually.

Well... you're making your job MUCH HARDER by doing it this way, and you're
not just violating the principle that fields should be atomic, you're
committing assault and battery on it! Storing multiple "notes" each with a
date and an identifier, all in one memo field, is both a chore and probably a
bad idea.

I'd really recommend restructuring to add a Notes table; this would have a
foreign key to the HealthHX table, a Date/Time field defaulting to Now(), a
NoteBy field defaulting to the current user's ID, and a memo field for the
text of the note. This will let you enter timestamped, identified notes into a
Subform with NO CODE AT ALL, and without the overhead and hassle of trying to
treat a memo field as if it were a relational table; it will also let you very
easily search for all of the notes for a case, OR all of the notes by a
particular person, OR all of the notes on a given date - none of which your
current structure will permit.

 

[Geezer via AccessMonster.com]

John, I understand what you're saying and I agree. I also think I wasn't
clear in my original post. This is a notes tables, Notes_HealthHX is a field
within the FollowUpNotes table. The update query that was run was simply to
populate this field with some pertinent information that had been gathered
before the FollowUpNotes table was created. It was essentially a one time
process to get information regarding the highest priority surgery comment
into Notes_HealthHX, which it did, but it put more information (I.E. more
than just the essential surgery. More information is better than not enough,
I suppose.)

The idea behind Notes_HealthHX was to have a single place to look for
relavent health history information when a subject is being called for an
annual follow up to see if their health has changed in the preceding year.
There is another "notes" field that is specific to each individual follow up
call, but certain information is being put in the Notes_HealthHX as an easy
"one stop" place to find critical health notes. Each subject has a single
Notes_HealthHX record, but will have an individual note, in the FollowUp
table, for each followup call made.

Given this explanation, do you still think we're going about this wrong?
I've seen your posts in this forum for years and certainly respect your
opinion. I'm not trying to argue the point at all, just learn so I don't
make a bad situation even worse. If this setup is acceptable, can you shed
any light on how I can use a query to remove the extra comments added to
Notes_HealthHX by the Update query. Keeping in mind this removal is also a
one time thing not something that needs done everytime a new surgery is
entered.

Thanks very much for any help you can provide!

Thanks Karl. The Notes_HealthHX is a memo field, there is one record per
subject and additional notes are simply added; it's not a new Notes record

[quoted text clipped - 3 lines]

records, that might already have two, three, four surgery Notes can be
cleaned up automatically or if it will need to be done manually.

Well... you're making your job MUCH HARDER by doing it this way, and you're
not just violating the principle that fields should be atomic, you're
committing assault and battery on it! Storing multiple "notes" each with a
date and an identifier, all in one memo field, is both a chore and probably a
bad idea.

I'd really recommend restructuring to add a Notes table; this would have a
foreign key to the HealthHX table, a Date/Time field defaulting to Now(), a
NoteBy field defaulting to the current user's ID, and a memo field for the
text of the note. This will let you enter timestamped, identified notes into a
Subform with NO CODE AT ALL, and without the overhead and hassle of trying to
treat a memo field as if it were a relational table; it will also let you very
easily search for all of the notes for a case, OR all of the notes by a
particular person, OR all of the notes on a given date - none of which your
current structure will permit.