Combine one item fields in one field

[romadm]

Hello, I am going deep to Access databases and have faced serious problem.

I have a table like:
Book1 | autohor11
Book1 | autohor12
Book1 | autohor13

How can I get report with authors combined in one field, like:


Book1 | author11, author12, author13

Groups will not help me, I need one TEXT within field, authors divided by comma.

Can you help me?

[kimmer]

How about a report that's Book Author Author etc, but no commas?

[romadm]

The problem is that I have another table:

Book1 | Description | Pages
Book2 | Description | Pages
Book3 | Description | Pages

and I need in result with join:
Book1 |Description|Pages|Author1, Author2
Book2 |Description|Pages|Author3, Author4

but no like
Book1 |Description|Pages|Author1
Book1 |Description|Pages|Author2.

I've managed to make some kind of inside report, but I always have authors in column, not in line:
Author1
Author2

If you tell me how to place them in line like Author1 Author2, I'll be happy=)

[kimmer]

• Create a query that produces a list of unique book titles (you can use DISTINCT).
• Make a report from the query. It should group by BookTitle and have a BookTitle footer.
• Create a subreport of authors, and use multiple columns... as many columns as is the max number of authors any book has. The columns need to be across then down. Remove everything from this report, leaving just a tiny Detail section with the Author textbox.
• Make the second report be a subreport of the first one... place this subreport in the BookTitle footer, and move the BookTitle textbox down into the BookTitle footer too.

The report must be viewed in Print Preview. It won't look right in any other view.

[romadm]

• Create a query that produces a list of unique book titles (you can use DISTINCT).
• Make a report from the query. It should group by BookTitle and have a BookTitle footer.
• Create a subreport of authors, and use multiple columns... as many columns as is the max number of authors any book has. The columns need to be across then down. Remove everything from this report, leaving just a tiny Detail section with the Author textbox.
• Make the second report be a subreport of the first one... place this subreport in the BookTitle footer, and move the BookTitle textbox down into the BookTitle footer too.

The report must be viewed in Print Preview. It won't look right in any other view.

I already have table of unique book titles, that's the table with description.

One question concerning subreport... I should not take multiple columns, there can be up to 30 authors, more over I must have an ability to export exactly that table with multiple Book Title concerning authors.

Can I do anything with this exact table?:
Book1 | autohor11
Book1 | autohor12
Book1 | autohor13

[kimmer]

I should not take multiple columns, there can be up to 30 authors, more over I must have an ability to export exactly that table with multiple Book Title concerning authors.

Can I do anything with this exact table?:
Book1 | autohor11
Book1 | autohor12
Book1 | autohor13

I apologize, but i do not understand. Please try again to explain. Have you even tried to make the subreport?

Perhaps when I said "multiple columns", you think I mean table columns, but I am talking about creating a multiple-column report... Design View of the report, Page Setup tab > Columns button.

But 30 authors! I don't know how that will look. It sure works well with 3 or 4 though.

[kimmer]

have a look at rptBooksDistinct in print preview

[romadm]

Thank you for explanation, and example! Really helps.

But one problem... I used books as an example. My table consists of 1500 "book title" records, and 5000 "authours".

This way to get what I need slows down my computer extremely. It takes several minutes for my Core2 to prepare report.

And I have 3 tables more, that should look like exactly "authors"... My computer will die=)

Can you advice quicker way? I thought we can do it by means of functions, but not reports...