I am having trouble figuring out how to concatenate results from a select function

[Jeff123]

Hello, I have a query that produces one column of five rows.

In each row, there is one sentence.


I would love if the sentences could be reported in one row, separated by periods.





Here is my SQL query:


SELECT TOP 5 Replace([TXT],"Member","Jeff") AS Expr1
FROM [ADHD/ODD]
ORDER BY Rnd(Int(Now()*id)-Now()*id);



An example of a current return would be:


Asked Jeff Jeff to list ways anger has negatively impacted daily life and health
Parent(s) were taught the importance of praise, support and encouragement.
Demonstrated for Jeff that counting backward from 10 to 0 is an effective strategy in remaining calm during an argument.
Jeff described progress of engaging in recreational activities as generally helpful.
Staff taught Jeff the reality of natural and logical consequences for disruptive and acting out behaviors.





I would love to have it return as:


Asked Jeff Jeff to list ways anger has negatively impacted daily life and health. Parent(s) were taught the importance of praise, support and encouragement. Demonstrated for Jeff that counting backward from 10 to 0 is an effective strategy in remaining calm during an argument. Jeff described progress of engaging in recreational activities as generally helpful. Staff taught Jeff the reality of natural and logical consequences for disruptive and acting out behaviors.



Unsure if this matters, but I run it in a report, and there would be margins.


I've searched the web until I'm blue, no luck. Any help is greatly, greatly appreciated! Thanks

[CJ_London]

I think this function will do what you require

http://allenbrowne.com/func-concat.html

you may need to modify it slightly to use periods

[Jeff123]

Hi, thanks for your reply. I had noticed this link on other searches, but it may not work for me as most query returns will be well beyond 250 characters. I was hoping that there is a syntax option to use in the SELECT line to have the query report in one row. Thank you!

[June7]

The +255 characters should not be an issue as long as you don't use the query in another query. The function can also be called from a textbox on report.

The only other way I know of to get these related records concatenated to a single value would involve writing to a temp table into a memo type field.