Updating a memo field

[Guest]

need help - want to update a memo field in this way:

original table has text in memo field - Notes 2004: xxxxxx

secondary table made from this table has the text from the original table in
memo field and then new text has been added in memo field - Notes 2004:xxxxx
Notes from Dean: xxxxx

original table has new text added (as both tables have been used for the
last two months) - Notes 2004: xxxxx need to buy xxxx of widgets.

What is the expression I use in the update query to accomplish the following:

Notes from original table and notes from secondary table without the
original notes:

Update column would look like this:

Notes 2004: xxxxx need to buy xxxx of widgets. Notes from Dean: xxxxx

 

[John Vinson]

need help - want to update a memo field in this way:

original table has text in memo field - Notes 2004: xxxxxx

secondary table made from this table has the text from the original table in
memo field and then new text has been added in memo field - Notes 2004:xxxxx
Notes from Dean: xxxxx

Well, you're violating two key rules here: your field is not atomic,
in that you're storing multiple distinct notes in the same memo field;
and you're storing data redundantly by storing the same note (in a
different way) in the primary and the secondary table.

original table has new text added (as both tables have been used for the
last two months) - Notes 2004: xxxxx need to buy xxxx of widgets.

What is the expression I use in the update query to accomplish the following:

Notes from original table and notes from secondary table without the
original notes:

Update column would look like this:

Notes 2004: xxxxx need to buy xxxx of widgets. Notes from Dean: xxxxx

That's going to be VERY DIFFICULT. Access does not see the memo field
as a series of separate notes; it sees it as a single,
undifferentiated block of text.

I suspect you will need to write some VBA code to extract the memo
field into a string variable, and parse it looking for the word
"Notes" (which appears to be the only delimiter). Of course if one of
the notes contains text like "I took careful notes on the quality of
the widgets" you'll have problems.

Any chance you could normalize your data, to give you a one to many
relationship from this table to a Notes table? Then instead of jamming
all the disparate notes into one Memo field, you could have each Note
in its own record, with other fields for the originator of the note,
the date it was added, etc.

John W. Vinson[MVP]

 

[Guest]

Thanks for the fast reply "John"

As it stands now, I have created a separate linked notes table to my main
database as each record for each company is from each individual year. (1994
to 2004) Each record could have up to 10 separate year note entries.
However, what I am trying to do is now keep all the notes for this year
(2004) which we are now working in, every so often merged into the main 2004
note record in the linked note table.

The notes that I want to merge, within the note itself, start with "Notes
from Joan:" It is from that point that I would like the notes to start and
add to the original notes. All the records have that "Notes from Joan:" as
a recognizable starting point within the memo field.

So it would be the "orginal notes" and then from the starting point of
"Notes from Joan;" that I want to accomplish. I could put in an ending
marker such as >>> if needed.

Could I write an update query of something like this:

[Original Table]![Notes field] + [secondary table]![notes field starting
with "notes from Joan" to ending marker of >>>]

Sounds probable to me, but is this possible? and if so, what would be the
expression with the expression builder?

Thanks for all your help.

 

[John Vinson]

Thanks for the fast reply "John"

As it stands now, I have created a separate linked notes table to my main
database as each record for each company is from each individual year. (1994
to 2004) Each record could have up to 10 separate year note entries.

Do you mean that you have ten Notes fields in each record? or ten
notes in a single memo field?

However, what I am trying to do is now keep all the notes for this year
(2004) which we are now working in, every so often merged into the main 2004
note record in the linked note table.

WHY?

You're making your life MUCH MORE DIFFICULT.

One note should be in one field in one record. "Fields are expensive,
records are cheap". Rather than putting multiple notes into one field
and then laboriously trying to pick them apart later, why not
normalize from the beginning, and have a one (company) to many (notes)
relationship? If you have 31 notes from Joan, you should have
*thirty-one* records in the Notes table, each with a single note. This
structure will work just fine for your system, and you won't need (and
*SHOULD NOT HAVE*) separate records for each year.

You're using a relational database; use it relationally! You'll find
it much more straightforward than using a denormalized structure (as
you are now) and trying to patch it up afterwards.

John W. Vinson[MVP]

 

[Guest]

"John"

Do you mean that you have ten Notes fields in each record? or ten
notes in a single memo field?

In my database for the year - I am linked back to my notes database - each
year is a separate record in the notes database - my database for the year
pulls up each year's notes (a record) into my form

WHY?

Trying to keep all the 2004 year notes in one record for the year - each
company, of which we have approximately 19,000, may have notes for 10 years -
therefore, at this point in time, trying to keep the notes down to the max
right now of about 10.

One note should be in one field in one record. "Fields are expensive,
records are cheap". Rather than putting multiple notes into one field
and then laboriously trying to pick them apart later, why not
normalize from the beginning, and have a one (company) to many (notes)
relationship? If you have 31 notes from Joan, you should have
*thirty-one* records in the Notes table, each with a single note. This
structure will work just fine for your system, and you won't need (and
*SHOULD NOT HAVE*) separate records for each year.

Each year's notes are in a separate record as it stands right now - that way
we are able to view from year to year what work has been completed with the
company. we were originally just having one memo field and importing the
notes from year to year, but that became too cumbersome - so we made a Master
notes database with the company names and company id numbers and then all the
notes for the years (three tables) and linked them all together

We now have our database for the year and we have linked back to the notes
database and show all the notes for each year - it is cleaner, and more
concise this way.

Now I would like to figure out how to take a section from inside a memo
field which has a beginning marked (>>>) and an ending marked (<<<). and
extract it and add it to another memo field. Wondering if it is possible and
if so, what expression should be used in the update query?

Marilyn

 

[John Vinson]

Now I would like to figure out how to take a section from inside a memo
field which has a beginning marked (>>>) and an ending marked (<<<). and
extract it and add it to another memo field. Wondering if it is possible and
if so, what expression should be used in the update query?

<sigh>

Ok, I've given my best advice: normalize your data and store it as
atomic, one note per record. You've rejected that advice. You're not
paying me for this so I'll just leave it at that.

It is not possible to extract the data between >>> and <<< with a
Query, especially if you mean to extract multiple notes from a single
field. You will have to write some rather complex VBA code to parse
the text in the memo field (a task which would not be necessary if you
used Access as it was designed). This code is (for me at least) beyond
the scope of unpaid volunteer work.

John W. Vinson[MVP]