How do I combine fields in a report

[Guest]

I have a book database which uses a subform for the authors. So I enter the
book information in the form and the authors in the subform. Of course, some
books have multiple authors. I'm wondering how I can combine the authors
with their books in a report. For example, the current report shows:

Author: Book:
Catherine Allen Title A
John Maurice Title B
Gary Smith Title A

Instead of Book A being listed twice, can I have the book listed once with
the 2 authors? Thanks so much!!

 

[Rick B]

You would use the group and sort options.

Group by Book Title and include a header for that section. In the header,
put the title. Then in the detail section, put the authors.

It would look something like this:


Title A
Catherine Allen
Gary Smith
Title B
John Maurice

(Of course, you don't have to indent the authors as I did.) You could also
leave space above the Book title, or include a footer for that group that
allowed spaces or lines.

Hope that helps,

Rick B

 

[Guest]

Thank you. Yes I have already created a report that is by title. However,
the one I'm inquiring about needs to be by author. Any other ideas?

 

[Rick B]

You said, "Instead of Book A being listed twice, can I have the book listed
once with the 2 authors?"

The solution I gave you does exactly that. It prints the book once, and
lists each author under it. Guess you will need to report your question if
that is not waht you are asking for.

Rick B

 

[Guest]

Try grouping by author, and putting the title in the detail section. Exactly
what Rick said, only different. ;-)

 

[Guest]

and by my understanding, that would be a report by title, rather than a
report by author! I have the report grouped by author and have the title in
the detail section but that is giving me the book listed twice under each
author's name. Instead I'd like it to show:

Catherine Allen & Gary Smith
Title A

Can a SQL statement do this?

 

[Guest]

That's how I have the grouping and sorting already. I'd like it to show:

Catherine Allen & Gary Smith
Title A

rather than:

Catherine Allen
Title A
Gary Smith
Title A

 

[Duane Hookom]

There is a generic contatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane that can
combine all the authors into a single expression.