Prev: Error in IIf Expression
Next: How to Populate a Table based on Entries in Another
From: Richard on 4 Jan 2010 14:00
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.

--
Richard
From: KARL DEWEY on 4 Jan 2010 14:28
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];


--
Build a little, test a little.


"Richard" wrote:

> 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.
>
> --
> Richard
From: Clifford Bass via AccessMonster.com on 4 Jan 2010 14:57
Hi Richard,

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

Clifford Bass

Richard wrote:
>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.
>

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1

 | 
Pages: 1
Prev: Error in IIf Expression
Next: How to Populate a Table based on Entries in Another