Pulling and Merging Data from Next **Valid** Record

[StephenB]

Cheers all.

• I received a table that broke a text field into several columns (text1, text2, etc.) Furthermore, once a certain textfield number (Text14) was reached, the programmer created a new record to continue the text...so text that would have gone into Text 15, went into a new record's Text1 field and continued from there.
• Each record is distiguished by different values in a "Line_Set_Num" field (record 1= 001, record 2=002, etc.) This is not a record ID.
• I managed to concantonate (sp?) the text in Text1-Text14 into a new field I created ("ALLText" - memo), but now need to merge the ALLText from from the multiple records and empty or remove the surplus records.
• I'm attaching a sample db that contains a table that represents the data as I recevied it. A second table with where I'm at with the data. And a third table dispalying the desired outcome. Please note how Line Set Numbers 002-004 have been removed in table 3.

Thanks in advance

 

[RuralGuy]

This link may get you a little further along on the process: http://www.baldyweb.com/PrevValue.htm

 

[StephenB]

Thanks RG. This is a good start.

 

[RuralGuy]

You're welcome. Just post back here if you need some more coaching.

 

[StephenB]

Again, thanks for the above. Due to time constraints I ended up cheating.

In a query I created a "key" field that was a result of concantotating three fields that made each batch of records unique. Then I filtered the query for line segment 001, and made a table based on those results. Then I revised the query by joining it to the newly created table on the new "key" field, and changed the filter to pull line segment 002, and set the query type to update. Now the query is set to update the new table. The update being:
qry joined tbl_new on "key"
where qry.linesegment = 002
update tbl.new.comments with (tbl_new.comments & qry.comments)

From there it was easy as pie when all I had to do was change the line segment to 003 and run. I did that until all comments were updated to line segment 001's comments.

RG's suggestin above was great and after playing around with it, I'veused it in other instances since then.