How do I put fields from multiple records into the same text box?

[gbgoates]

I have a database with about 10 categories (the names of which are in a table with 10 records), about 100 sub-categories (the names of which are in a table with 100 records, each having a link to one of the categories), and about 100 items (the names of which are in a table with 1000 records, each having a link to one of the subcategories). I can simply use the query wizard to generate a report that groups by category and then by subcategory and then for each item lists the name and another parameter (which sometimes is non-existent).

However, that is not quite what I want. Such a report takes up one line per item (ignoring the lines for the category and sub-category headings), which takes over 20 pages. That is too many pages and people are not going to bother to look at it.

What I need is a text box for each sub-category that contains the names of each item within it, separated by semicolons, and if the other parameter is there that value in parentheses between the item name and the semicolon. I know want a "Can Grow" format property on the text box such that the item names and optional parameters within it wrap, and the text box takes up as many lines as needed to fit all that. But I don't know how to get the data into the text box.

Any suggestions?

Thanks in advance,
gary

 

[JerryDennison]

How about a subreport that uses columns instead? It's a little difficult to explain but it's really quite easy and very elegant. You would base the subreport on the table or query that has the detail records(the one's you're trying to concatenate into a single record). Create the report without any header or footer. Add the linking field and the field to display to the recordsource(if other than the table). Place the display field on the report and size it to an appropriate width. Change the number of columns to maximize your report real estate(this in under File... Page Setup... Columns...). Close and save the report. Open your main report in design view. Drag and drop the subreport onto the detail section. You'll probably have to play with the width and such to get the results you want visually. You may also have to force the link master/child fields if no apparent relationship already exists.