Concatenating fields

[DanKay1]

Can any please help with concatinating several message fields into the 1 row.
from Table1 -> Table2
I have wrote the code but it doesnt work properly. Can you please check my logic or suggest your solution. Thank you.

Table 1
Policy Message
01 msg1
msg2
msg3
02 msg1
03 msg1
msg2


Table2
Policy Message
01 Msg1,Msg2,Msg3
02 Msg1
01 Msg1,Msg2

While Not tabl1.EOF()

If (Len(table1.policy) = 2) Then
LastPolicy = table1.[policy]
MessageStr = table2.[Message]

While Len(table1.policy) = 2
MessageStr = MessageStr + table1.[Message]
table1.MoveNext
Wend

With table2
.AddNew
.Fields("Policy") = LastPolicy
.Fields("Message") = MessageStr
.Update
End With

End If
Table1.MoveNext
Wend

 

[HitechUser]

DanKay1,

If I understand you requirement correctly... Then try this.

http://www.tek-tips.com/viewthread.cfm?qid=563648

It did the trick from me..

If I got it backwards then let me know..

Good Luck..

 

[DanKay1]

Well policy number don't repeat, thats what makes it a bit more complicated.

 

[Golom]

I infer that so far, you've been lucky.

Relational databases don't guarantee any particular ordering of records when they are returned if you don't use an ORDER BY clause. You appear to be counting on the fact that the two records (for example) with blank or NULL "Policy" fields that follow the record with Policy = 1, also apply to "Policy = 1" and you're depending on their always being returned in that specific order.

As I said, there's no guarantee and at some far-off distant time in the future (or the next time you run it) they may be returned in some other order and your relationship will be destroyed.

As long as they are coming back in that order, I suggest that you put the correct codes on the records that don't have one before some great evil happens and destroys your implicit ordering.


Once you've done that then HiTechUser's suggestion should deal with the question that you asked.

 

[DanKay1]

I have Primary key which keeps them in order.

 

[PHV]

And something like this (typed, not tested) ?
With table2
While Not table1.EOF()
If (Len(table1.policy) = 2) Then
.AddNew
.Fields("Policy") = table1.[policy]
.Fields("Message") = table1.[Message]
.Update
Else
.Fields("Message") = .Fields("Message") & table1.[Message]
.Update
End If
Table1.MoveNext
Wend
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244