Concatenate Comment Rows

[SeanCly10]

Hi all,

I'm not sure if this part of the forums is the place for this question, but here goes anyway.

I am trying to concatenate a series of sequentially numbered comment fields contained in a single table. It looks like this:

71478 1 I want to
71478 2 combine all
71478 3 of these lines
71478 4 into the same
71478 5 field so
71478 6 a person can
71478 7 read it

I'm pretty certain I need VB code, but how to write it is still a little beyond me. Can someone give me some pointers?

Thank you,
Sean

 

[pbaldy]

A function along these lines should work:

http://www.mvps.org/access/modules/mdl0004.htm

You'd want to make sure the recordset pulled in the correct order.

 

[Beetle]

Have a look at Allen Browne's Concatenate function.

 

[SeanCly10]

Hey, it worked pretty good! Thanks!

One other thing, though: for comment series where there is 10 or more, the code tries to put 10 immediately after 1. I know this is a basic Access issue and not a problem of the function, but might someone know how to make the thing properly order the numbers?

 

[pbaldy]

It sounds like that sorting field is text rather thn numeric. If so, you can either change it or force it in your function with CInt().

 

[SeanCly10]

Well, I knew it was too good to be true.

The table I'm working with has multiple accounts in it, each with multiple comment lines. The function will concatenate the comment fields, sure, but it only does it for the first one and puts that result in the field for each account, and not just the related one; i.e., Account A's comment is reproduced in the comment field of Accounts B, C, and D, as well as A.

Am I missing something?

 

[pbaldy]

Your SQL would need to restrict to a given account. I'm sure both of the functions posted allowed for that.

 

[SeanCly10]

They probably do, but I can't figure out how to alter them to do it. I keep getting the same result, one combined comment field repeated for every account.

 

[pbaldy]

We're flying blind here. Maybe you can post the db here, or at least the code and how you're calling it?

 

[SeanCly10]

Okay. I'm using the Concatenate function that Beetle posted.

Here is the (greatly simplified) SQL for the query:

SELECT ConcatRelated("RMSTRXNOTE","TestTable","RMSFILENUM = left(SEQUENCE,6)","SEQUENCE","") AS Expr1
FROM TestTable;

TestTable.xls is the entry data. the column of SEQUENCE is an attempt to make each line truly unique - including the file number, transaction date and time, plus the sequence number. TestTable_Result.xls is the result of the query.

 

[pbaldy]

Does this work?

SELECT ConcatRelated("RMSTRXNOTE","TestTable","RMSFILENUM = '" & [RMSFILENUM] & "'","SEQUENCE","") AS Expr1
FROM TestTable;

 

[pbaldy]

By the way, I suspect you'd actually want something like this:

SELECT TestTable.RMSFILENUM, ConcatRelated("RMSTRXNOTE","TestTable","RMSFILENUM = '" & [RMSFILENUM] & "'","SEQUENCE","") AS Expr1
FROM TestTable
GROUP BY TestTable.RMSFILENUM;

 

[SeanCly10]

Bloody wonderful. Works like a charm. Thank you all!

 

[pbaldy]

Happy to help!