A memo field in my table contains a list of products, with one product
occupying a row in the field (not an observation in the table). Now I want to
remodel this table so that one product actually becomes an observation. In
other words, I need to converts the text in the memo field into tables, using
the paragraph mark as the delimiter. Below illustrates the issue:

Original table
The desired table
Supplier Product List (memo) Supplier Product
List (text)
---------- ------------------------- ----------
-------------------------
A Product 1 A
Product 1
Product 2 ----------
-------------------------
---------- ------------------------- A
Product 2
B Product 3 ----------
-------------------------
Product 4 B
Product 3
---------- ------------------------- ----------
-------------------------
… …

Is that doable? Many thanks.

Lafayett

Sorry, the example didn't display properly in the main entry. Here is another
try:

Original table
Supplier Product List (memo)
---------- -------------------------
A Product 1
Product 2
---------- -------------------------
B Product 3
Product 4

---------- -------------------------
…

The desired table
Supplier Product List (text)
---------- -------------------------
A Product 1
---------- -------------------------
A Product 2
---------- -------------------------
B Product 3
---------- -------------------------
…

Look forward to your suggestion. Thanks a lot.

Lafayette


"Lafeyette" wrote:

> A memo field in my table contains a list of products, with one product
> occupying a row in the field (not an observation in the table). Now I want to
> remodel this table so that one product actually becomes an observation. In
> other words, I need to converts the text in the memo field into tables, using
> the paragraph mark as the delimiter. Below illustrates the issue:
>
> Original table
> The desired table
> Supplier Product List (memo) Supplier Product
> List (text)
> ---------- ------------------------- ----------
> -------------------------
> A Product 1 A
> Product 1
> Product 2 ----------
> -------------------------
> ---------- ------------------------- A
> Product 2
> B Product 3 ----------
> -------------------------
> Product 4 B
> Product 3
> ---------- ------------------------- ----------
> -------------------------
> … …
>
> Is that doable? Many thanks.
>
> Lafayett
>

On Thu, 18 Jun 2009 09:48:01 -0700, Lafeyette
<(E-Mail Removed)> wrote:

>Sorry, the example didn't display properly in the main entry. Here is another
>try:
>
> Original table
>Supplier Product List (memo)
>---------- -------------------------
>A Product 1
> Product 2
>---------- -------------------------
>B Product 3
> Product 4
>
>---------- -------------------------
>…
>
> The desired table
>Supplier Product List (text)
>---------- -------------------------
>A Product 1
>---------- -------------------------
>A Product 2
>---------- -------------------------
>B Product 3
>---------- -------------------------
>…
>
>Look forward to your suggestion. Thanks a lot.

That's going to require some VBA code to parse through the text, looking for
vbCrLf (new line) delimiters, and parsing each row into fields: i.e. nothing
automatic and probably not trivial. I wonder if a "cheat" might work - you
could copy and paste the entire memo field into an Excel spreadsheet, clean up
the separator hyphens, and extract the rows and columns back into Access.
--

John W. Vinson [MVP]

Thanks for the answer. Confirm my bad feeling, though. Going to be a labor
intensive work :-(

"John W. Vinson" wrote:

> On Thu, 18 Jun 2009 09:48:01 -0700, Lafeyette
> <(E-Mail Removed)> wrote:
>
> >Sorry, the example didn't display properly in the main entry. Here is another
> >try:
> >
> > Original table
> >Supplier Product List (memo)
> >---------- -------------------------
> >A Product 1
> > Product 2
> >---------- -------------------------
> >B Product 3
> > Product 4
> >
> >---------- -------------------------
> >…
> >
> > The desired table
> >Supplier Product List (text)
> >---------- -------------------------
> >A Product 1
> >---------- -------------------------
> >A Product 2
> >---------- -------------------------
> >B Product 3
> >---------- -------------------------
> >…
> >
> >Look forward to your suggestion. Thanks a lot.
>
> That's going to require some VBA code to parse through the text, looking for
> vbCrLf (new line) delimiters, and parsing each row into fields: i.e. nothing
> automatic and probably not trivial. I wonder if a "cheat" might work - you
> could copy and paste the entire memo field into an Excel spreadsheet, clean up
> the separator hyphens, and extract the rows and columns back into Access.
> --
>
> John W. Vinson [MVP]
>