agregate fctions for Strings?

[Guest_imported]

Hello

I have a Table in Ms access representing messages:

Messages(MessageId:int,WordNumber:int,Word:text)

a message, represented by MessageId, is composed with words numbered with WordNumber. I would like to retrieve each message as a string. Is it possible?

 

[ThomVF]

Since you dont know how many words there are in each message (I'll also assume that each Message can have a variable number of words), you probably have to do this in a procedural fashion.

I can show the pseudo-code/logic but I do not know Access well enough to tell you it can be done. It could be done in ORACLE or SQL-Sever using procedures or a recursive function.

Procedurally:

generate a cursor of all Messages records
ordered by MessageID, WordNumber;

get first MessageID, Word;
While cursor of records is not done, LOOP:
While MessageID has not changed, LOOP:
MsgString := MsgString + Word;
get next MessageID, Word;
End-While
Print MsgStrng;
End-While


Recursively:

-- assumes word number is sequential and no gaps
For each unique MessageID LOOP
call function MakeString(MessageID,1,'');
End-Loop

Function MakeString (@MsgID, @WordNum, @MsgStr)
Get Word from Messages
where MessageID = @MsgID
and WordNumber = @WordNum;

If no record then return @MsgStr; -- exit cond
else
@MsgStr := @MsgStr + Word;
-- make recursive call
MakeString(@MsgId, @WordNum+1, @MsgStr);

End-Function
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[Guest_imported]

Thanks, i also though i would have to do like that.
My problem is that i must be able to make statistics for messages on a word-basis, So the structure of the table is appropriate. But i must also be able to make pattern matching on each message text, so i must somehow construct the string containing the message.
Maybe i should save each message as a text also...
Anyway thx for the answer

 

[adam0101]

If you did a cross tab query, may you could loop through the result and concatenate the columns together.

TRANSFORM Max(Word) AS MaxOfWord
SELECT MessageID
FROM Messages
GROUP BY MessageID
ORDER BY WordID
PIVOT WordID;