Concatenate multiple records into one textbox (1 Viewer)

[JamesMcS]

Hi everyone, hope the weekend was fun....

I've got a report which lists sales figures by Brand. Under Brand there are a certain amount of manufacturer numbers, the table is organised thusly:

Brand_ID, Manufacturer Number
1, 12345
1, 67890
1, 09876
1, 54321
2, 55555
2, 66666
2, 77777

So, what I want to do is have one text box in the header of the report to show the manufacturer numbers that fall under that brand, separated by a comma.

Is this possible? My first thought was reading each manufacturer number into an array of some sort but I can't quite see it clearly in my head.... would that be the right way to go?

 

[Kryst51]

Just a thought, but what about creating a cross-tab query, then pulling that query into another query, where you can then created the concatenation?

 

[JamesMcS]

Thanks for the reply Kryst - I must be a bit slow today (Monday and all) but I can't see what you mean...

To clarify - the report will show the net sales for brand 1. In the report header, I'd like to see a text box with "12345, 67890, 09876" in it.

I'm not sure how a crosstab query would help here....

 

[Kryst51]

OK, I always misunderstand how a crosstab is used. So I tried to do my own suggestion, and it doesn't work... So I googled "Concatenate query results" and came up with this link.

It's by Allen Browne, so its reliability is sound. I hope it helps.

 

[JamesMcS]

You are absolutely wonderful! Thanks mucho! I'll get going on this now.

 

[Kryst51]

You are absolutely wonderful! Thanks mucho! I'll get going on this now.

You are so welcome!