Extract data from memo field and put into new table (1 Viewer)

[jom1918]

Is there a way I can take each entry in a memo field and put it into a text field in a separate table. The database is getting really big and the customer notes field for each record has lots of entries. The memo field looks like this:

8.4.14 Ordered 2 cartons
20.3.14 Ordered 2 cartons
4.3.14 Ordered 2 cartons
18.2.14 ordered 1 carton
30.1.14 ordered 3 cartons SCENTED wipes
14.1.14 ordered 1 carton
12/12/13 - Ordered 3 cartons to get through Jan 13.
26/11 - Ordered 2 cartons
12/11/13 - one carton
28/10/13 - Mandy still has two cartons.
15/8/13 – Natalie ordered 1 ctn Uns.

I want to take each line and put put the date in a date field and the text in a text field in a separate table linked by CustID. Is there a way to do that?

 

[JHB]

Use a recordset to loop through the table with the memo field, and the Split function to separate each entry in the memo field from each other + an append query for inserting data in the other table.

 

[jom1918]

Thanks JHB, but I do not know how to do what you are advising me to do.
I don't know how to use a recordset to loop through the table to find each piece of the notes data between the <Div> and </div> text.

Split function in my version of access seems to be about splitting the database so there is a front end and a back end;

I can do the append query once I get to a point where I am selecting the correct data. I just don't know how to get there.

Can you give me an example of something to help me get started because I am not a programmer and have to work within the limits of an existing Access database that wasn't written by me. I really need some help.
Thanks

 

[JHB]

Do you have some sample data in a MS-Access table you could post?

 

[jom1918]

I have attached the customer table with 2 records and the relevant fields in an excel spreadsheet. The ID type fields are number - long integer and the notes field is the memo field.

I have a tbale for contacts with date, time, note1 and note2 text fields rather than memo fields to store the notes. Each text field is 255 char long. I want to put each note by date in this contact table.

Hope this makes sense to you.

 

[JHB]

I've made some sample for you in the attached database, open the only form in it, click the button, the raw data is loaded from the table "AW Customers" and the result goes into the table "SplittedTextTable".

 

[jom1918]

Thank you so much. This is great and I understand it. YOu are a gem JHB

 

[JHB]

Thank you so much. This is great and I understand it.

You're welcome, and good you understand how it works!