Combine results for a field on a report

[badinjector]

I'm having trouble figuring this one out...i have to conform to a pre written document that has the following structure:
Category 1 Category1text
subcat 1 subcat1text #products go here in this col#
subcat 2 subcat2text #products go here in this col#
The problem is the form was not designed with data access in mind.The #products go here column is for purchased products# it has space to write..yes i said write..in multiple products (ok they arent products but it'll do for explanation purposes) I made some tables like this:
tblCategory catid, cattext
tbl subcat subcatid, subtext, catid
tblproducts prodid, prodtext, subcatid

If i try to print out #products#, i get the following

Cat blah blah
subcat1 blah blah #product 1#
subcat1 blah blah #product 2#
subcat1 blah blah #product 3#
subcat2 blah blah #product 1#
subcat2 blah blah #product 2#

I then set the duplicates property so that the following happens:
subcat1 blah blah #product 1#
#product 2#
#product 3#
subcat2 blah blah #product 1#
#product 2#

Pretty sson after ten or so "products" i'm quickly running out of room (there are 6k subcat prod pairs). Soooo...the question is:

Is there any way i can combine the output to get:

subcat1 blah blah #product 1#,#product 2#,#product 3#

that is concatenate the #products# into one string as above in one text box so that the empty space is reduced? i can have it spill over onto two or three lines at most. The font size for the last column is 8, and i can fit about three products in the space before it gets silly looking because the subcat text needs to be visible at least once and sometimes that text is about two or three lines long. That means the first #prod# will be beside the first line, but then two lines are skipped before the next one is spit out, then with hide duplicates, a bunch of blank space and so on and so on..

 

[Remou]

Have a look at this:
How to concatenate multiple child records into a single value
faq701-4233