Concatenate all rows for a report field into a multi-line text box.

[Robeen]

Hi,

I have a report that has 4 levels of grouping.
The last group is Branch.
For each Branch - the report displays detail rows [Name & $ Amount] for customers who meet the criteria set by the query off which the report runs.

I have a request from the report user to concatenate all the Names and $ Amounts for each Branch wrapped-around into one text box so it would look more like this:

Branch A

Name1 $1234.00, Name2 $1234.00, Name3 $1234.00,
Name4 $1234.00, Name5 $1234.00, Name6 $1234.00, ...

than what it looks like now:

Branch A

Name1 $1234.00
Name2 $1324.00
Name3 $1234.00

. . . you'll have to excuse how I did the above . . . it's not exactly how the report looks but it's as close as I can get.

If I can get the detail concatenated - the user can display Branches with a lot of customers in a smaller space.



Q 1. Can I get my report to do this?
Q 2. If not, can I get the query to do this?
Q 3. If not . . . I guess I have to code it?

I'd appreciate any suggestions.

Thanks!

Robin

[pbaldy]

Does this work for you?

Return a concatenated list of sub-record values

[pbaldy]

Have you looked at columns in the report by the way?

[Robeen]

Hi Paul,
I'm not sure I understand what you mean when you ask if I've looked at columns in the report. Could you elaborate for me please?
Robin

[pbaldy]

In Page Setup is a Column tab. You can have a report that normally goes down like your current data and put it into columns, so

Name1 $1234.00
Name2 $1324.00
Name3 $1234.00

becomes

Name1 $1234.00 Name2 $1324.00 Name3 $1234.00

[Robeen]

Sorry Paul.
I still don't get it.
I opened up the Page Setup and the Columns Tab - but don't know what to do once I'm in there! Sorry!

My 'Name' and 'Amount' are two fields on the report.
I can make them into a single field in the query - but how do I use the 'Columns' tab to get all the name & amount values into one box?

[pbaldy]

Using the Columns settings wouldn't put them into one box. You'd set up your report with those 2 fields (or 1 combined field), so that when you viewed it they just ran down the page. When you change the info in columns, Access will take the single column and create columns.