How to concatenate remarks for a given purchase request

[Richard]

In our financial management system, a user can add multiple remarks for every
purchase request.

So for example, I have a table that looks like
Input:
Purchase_req_no remark_no remark
---------------------------------------------
PRN_100 1 "first remark line for 100"
PRN_100 2 "second remark line for
100"
PRN_100 3 "third remark line for
100"
PRN_200 1 "first remark line for
200"
PRN_200 2 "second remark line for
200"


How do I compose a sql query where the output has all the remark lines
concatenated?

Output:
Purchase_req_no remarks_concatenated
PRN_100 "first remark line" + "second remark line" +
"third ...
PRN_200 "first remark line" + "second remark line"


It would be great if the concatenated output had a single space between each
remark.
For a single purchase_req_no there can be up to 10 remark lines numbered 1
throuth 10, and another ten numbered 900 through 910; it would be good if the
query added only lines less that 900.

 

[KARL DEWEY]

Try this, continuing for as many remarks as you may have --
SELECT Purchase_req_no, YourTable.remark & " " & YourTable_1.remark & " " &
YourTable_2.remark & " " & YourTable_3.remark
FROM ((YourTable LEFT JOIN YourTable_1 ON YourTable.Purchase_req_no =
YourTable_1.Purchase_req_no AND YourTable.[remark_no]+1 =
YourTable_1.[remark_no]) LEFT JOIN YourTable_2 ON YourTable.Purchase_req_no =
YourTable_2.Purchase_req_no AND YourTable.[remark_no]+2 =
YourTable_2.[remark_no]) LEFT JOIN YourTable_3 ON YourTable.Purchase_req_no =
YourTable_3.Purchase_req_no AND YourTable.[remark_no]+3 =
YourTable_3.[remark_no];

 

[Clifford Bass via AccessMonster.com]

Hi Richard,

Take a look at Allen Browne's solution at <
http://allenbrowne.com/func-concat.html>,

Clifford Bass