Convert text into tables

[Lafeyette]

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

 

[Lafeyette]

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

 

[John W. Vinson]

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.

 

[Lafeyette]

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