how do I extract pieces of data from a field

[Bill WP Brown]

I have a field named CntItmPrgs_Interactions which contains Scorm training
data.
I need to be able to extract elements from the field. A sample of the data
is copied below. I need to be able to extract certain elements into seperate
fields for example;
For id="int0" - id0 will become field name
learner_response="" will become the field content

For id="int1" - id1 will become field name
learner_response="" will become the field content

A single field could hold up to 18 IDs. The sample below shows the first 3.




<interactions><interaction index="0" id="int0" timestamp="" weighting="0"
type="choice" latency="" learner_response="2"
result="correct"><objectives/><corr_resps><corr_resp index="0" id=""
pattern="2"/></corr_resps></interaction><interaction index="1" id="int1"
timestamp="" weighting="0" type="choice" latency="" learner_response="2,3"
result="correct"><objectives/><corr_resps><corr_resp index="0" id=""
pattern="2,3"/></corr_resps></interaction><interaction index="2" id="int2"
timestamp="" weighting="0" type="choice" latency="" learner_response="0"
result="correct"><objectives/><corr_resps><corr_resp index="0" id=""
pattern="0"/></corr_resps></interaction><interaction index="3" id="int3"
timestamp="" weighting="0" type="choice" latency="" learner_response="0,2,3"
result="correct"><objectives/><corr_resps><corr_resp index="0" id=""
pattern="0,2,3"/></corr_resps></

 

[Jerry Whittle]

First things first. id0 nor any of the other ID's should become a field
name. That's thinking across like a spreadsheet.

Instead you need to think down like a database. In fact your sample data
seems to be set up properly if it was in different fields instead of mashed
into on. You want fields such as ID, timestamp, weighing, etc.

It almost looks like the sample data below could be imported into a table,
but not quite. There doesn't seem to be any end of record markers like a
paragraph or line return.

Did you get this data from another system? Possibly it could export the data
into something that Access could use like delimited text or a comma seperated
value (CSV) file.

 

[John W. Vinson]

On Tue, 9 Feb 2010 03:58:01 -0800, Bill WP Brown <Bill WP

I have a field named CntItmPrgs_Interactions which contains Scorm training
data.
I need to be able to extract elements from the field. A sample of the data
is copied below. I need to be able to extract certain elements into seperate
fields for example;
For id="int0" - id0 will become field name
learner_response="" will become the field content

For id="int1" - id1 will become field name
learner_response="" will become the field content

A single field could hold up to 18 IDs. The sample below shows the first 3.




<interactions><interaction index="0" id="int0" timestamp="" weighting="0"
type="choice" latency="" learner_response="2"
result="correct"><objectives/><corr_resps><corr_resp index="0" id=""
pattern="2"/></corr_resps></interaction><interaction index="1" id="int1"
timestamp="" weighting="0" type="choice" latency="" learner_response="2,3"
result="correct"><objectives/><corr_resps><corr_resp index="0" id=""
pattern="2,3"/></corr_resps></interaction><interaction index="2" id="int2"
timestamp="" weighting="0" type="choice" latency="" learner_response="0"
result="correct"><objectives/><corr_resps><corr_resp index="0" id=""
pattern="0"/></corr_resps></interaction><interaction index="3" id="int3"
timestamp="" weighting="0" type="choice" latency="" learner_response="0,2,3"
result="correct"><objectives/><corr_resps><corr_resp index="0" id=""
pattern="0,2,3"/></corr_resps></

This looks *sort of* like XML (which recent versions of Access can read). Is
it? or is it HTML, or some custom format?

What's the actual structure of the table into which you are trying to insert
this data?