Prev: Crosstab query...
Next: Can not group on fields selected with '*'
From: fishy on 24 Jan 2010 01:59
I have inherited a database where someone in their infinite wisdom asked
staff to put four key fields into a memo field and separate them with a line
break.

I am trying to tidy up the field so that I can select the data (if it
exists) into four fields:

Date of payment.
Net amount.
Tax amount.
Interest added.

As a second complexity the data doesnt exist in all of the records i.e. the
member of staff may not have required to have input interest, have put the
heading in different orders or no descriptive of the amounts. I have tried to
input wildcards but dont know where to start in terms of finding the monetary
amount that follows this descriptive and assume that I will probably have to
run several versions of the query until I get all of the variants out.

The database is large so am trying to find a way of running an update to
field and delete from the memo field rather than have to wade in the old
fashioned way. Even if I could get a large chunk of the data into the new
fields and deleted from the existing field then that would be a great help.

All I can say is thank heavens for backups as I have tried several times
with poor results. The only guarantee is locating the date but how do I get
the query to take out the descriptve AND the date and only append the date to
the date field?
From: Jeff Boyce on 24 Jan 2010 10:27
This approach may not be pretty (i.e., "elegant"), but it might work
(untested!)...

Export the memo fields and their respective rowIDs to Excel.

Try using Excel's parsing function(s) to split the memo field into (as many)
fields.

Import the parsed fields (and rowIDs) into Access.

Use queries to "distribute" the imported data as appropriate.

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"fishy" <fishy(a)discussions.microsoft.com> wrote in message
news:ECFC7A4F-3023-495C-95BC-2BF5A43CBE0D(a)microsoft.com...
>I have inherited a database where someone in their infinite wisdom asked
> staff to put four key fields into a memo field and separate them with a
> line
> break.
>
> I am trying to tidy up the field so that I can select the data (if it
> exists) into four fields:
>
> Date of payment.
> Net amount.
> Tax amount.
> Interest added.
>
> As a second complexity the data doesnt exist in all of the records i.e.
> the
> member of staff may not have required to have input interest, have put the
> heading in different orders or no descriptive of the amounts. I have tried
> to
> input wildcards but dont know where to start in terms of finding the
> monetary
> amount that follows this descriptive and assume that I will probably have
> to
> run several versions of the query until I get all of the variants out.
>
> The database is large so am trying to find a way of running an update to
> field and delete from the memo field rather than have to wade in the old
> fashioned way. Even if I could get a large chunk of the data into the new
> fields and deleted from the existing field then that would be a great
> help.
>
> All I can say is thank heavens for backups as I have tried several times
> with poor results. The only guarantee is locating the date but how do I
> get
> the query to take out the descriptve AND the date and only append the date
> to
> the date field?


 | 
Pages: 1
Prev: Crosstab query...
Next: Can not group on fields selected with '*'