Append Riddle

[magmike]

I would like to combine records in the Notes table WHERE they relate
to a CompanyID, into one record, when they all share the same date.
Since they are all time stamped as well, I'm hoping they could be
appended in the proper order.

For further understanding, let me explain why. Using a model I was
familiar with at an old company, I designated that each note could not
be more than 50 characters. So in order to capture longer thoughts,
the user would have to create multiple records. Since they display in
order in a continuous form, it flows fine. However, it has been, in my
opinion, a bad way to handle notes. So I have changed the field to a
memo in the table and am now allowing a full note field.

So the above request is aimed at combining those limited, 50
charachter note "sessions" into one record per company per date. If it
is possible, I'm guessing it would be a good safety net to add a space
on each end of each record (to avoid combined words), and then
afterward do a find/replace for double, triple and quadruple spaces
with one space, as well as period for space period.

Is that a possibility?

Thanks in advance!
magmike

 

[Clifford Bass]

Hi magmike,

Definitely possible. It probably is easiest to do it in code. I
presume that you still have a separate notes table. In English it would look
something like this:

Open notes table, ordering by CompanyID and date/time.
Loop through it, paying attention to when the CompanyID and/or the date
change, creating your new note in a string variable.
When one of those changes, do your replaces in the string and then add the
new item to your new notes table.
When all done looping, add the final item to the new table.

You should test on a test copy of the database before doing for real on
the read data so that you know it works correctly.

Hope that helps,

Clifford Bass

 

[magmike]

Himagmike,

     Definitely possible.  It probably is easiest to do it in code.  I
presume that you still have a separate notes table.  In English it would look
something like this:

Open notes table, ordering by CompanyID and date/time.
Loop through it, paying attention to when the CompanyID and/or the date
change, creating your new note in a string variable.
When one of those changes, do your replaces in the string and then add the
new item to your new notes table.
When all done looping, add the final item to the new table.

     You should test on a test copy of the database before doing for real on
the read data so that you know it works correctly.

              Hope that helps,

                     Clifford Bass









- Show quoted text -

Could you point me to a couple of sources that will teach me the
concepts I need to learn to turn that english into code? I assume
looping (and I'll need to study that) but what else outside of basic
coding?

 

[Clifford Bass]

Hi magmike,

Outside of basic BASIC coding you will need to use either DAO or
ADO in order to use a recordset. I prefer ADO, but others like DAO. ADO is
used a lot in the .NET programming environment. The online help in Access
2003 or 2007 is a good place to start. While in the Visual Basic Editor
choose "Microsoft Visual Basic Help" from the Help menu. It will give you a
contents page/list from which you can get tons of information on ADO, DAO and
other concepts. Or, there are lots of books on Access out there. I am sure
there are lots of web sites. If you do a search for "Microsoft Access" you
will get a lot of options. One site that comes to mind is
http://www.w3schools.com/ado/default.asp for ADO.

Good luck! If you get to knotty problems, post again.

Clifford Bass