Hi all,
I have a question related to strings. We have shipments with remarks but these remarks are covered over 10 lines. I dont want to have a dataset with the same shipmentnumber 10 times with the remarks text in 10 records but one line with the shipment number and one field with the complete text.

I have 3 columns called:
[number] [Sequence] [text]
0 0 abc
0 1 def
1 0 ghi
1 1 jkl
1 2 mno

I would like to see another column [textline] with the text joined together but according to this example (taking the group into account).then I will use a Max on sequence to get only one record with the complete tekst. or an alternative which I havent considered what could work maybe better as mine idea.

[number] [Sequence] [tekst] [textline]
0 0 abc abc
0 1 def abcdef
1 0 ghi ghi
1 1 jkl ghijkl
1 2 mno ghijklmno


result wil be:

[number] [textline]
0 abcdef
1 ghijklmno

best regards,

Paul.

Perhaps someone else could come up with a clever query (or set of queries) to solve this problem, but I would just be inclined to write a loop in VBA code to go thru each of the records and create a brand new table. Just roughing out a bit of pseudo code (I leave it to you to get the syntax right):

       rs = db.recordset("Your table here")
       PrevNum = null
       textline = ""
       for r=1 to nRecords
          if rs("Number") <> PrevNum then
             if PrevNum is not null then
                create new record with PrevNum & textline
             PrevNum = rs("Number")
             textline = rs("text")
          else
             textline = textline & rs("text")
          end if
       next r

You should realize that I am being very sloppy here (don't forget the last [number] in the table). Of course if the table is not sorted by [number] that would be another problem. The simple solution to this is to not use the table itself as input but a query based on the table which is sorted by [number].

-Phil-