Loop Query Question

[Megawisdumb]

I’ve got a basic access loop question. I tried various recordsets that start with the CLP segment and pull all the data until the next CLP segment into a single record line to no avail. From the example below, I want the data from row 19,22 & 23 on the same line and then move to the next CLP. Is there a simple do-while type select statement to build this recordset?

ID /Segment/ Identifer/ Claim Status/ Total Charge /Total Payment/ Field6/ Code/ TCN /Field9/ Med#
19/ CLP/ 401222220/ 19/ 97/ 62.35/ MA/ 204
22/ NM1/ QC /1 /edwards bill /J/ HN /403333220A
23/ NM1/ TT/ 2 /
50/ CLP /407548229/ 19/ 1409/ 905.85/ MA /20434100000602/ 75
53 /NM1/ QC/ 1/ toys joe/ M /HN /407778229A
54/ NM1/ TT/ 2/ DEPT F

The above display is the output of a query. I want to merge ID's 22 & 23 into row 19 with the CLP segment incidating a new row. Therefore, if the new recordset is successful I will end up with only two rows starting with CLP segments that reflects all the data elements above. Does that make more sense?

 

[The_Doc_Man]

This is a concept problem. SQL originally, a long time ago, got its name from "Sequential Query Language" though I'm told other names sometimes seem to apply now.

You are doing something that ISN'T a sequential operation.

What you are trying to do is to do DIFFERENT things to records that differ in some way, but SQL is designed to more or less do the SAME things to every record in its recordset. It steps sequentially through the records, doing its thing each time. But you don't want that. Which means you really don't want SQL, either. This is a VBA-class problem.

You can still use an SQL query to provide the recordset, but your desire to selectively combine records means you need to do more than an ordinary query would do.