Concatenating values from a many relation

[Arts]

I have two tables with one to many relation between them. Say, table Book
has a bookID and are related to a table Author with many authors in one book
like this:
Book: BookID Title
1 Jumping
2 Working
Author:
BookID Author
1 Fred
1 Mari
2 Else
2 Ann

I want to write a query that shows only one row per book with all authors in
one field like this:
BookID Title Author
1 Jumping Fred, Mari
2 Working Else, Ann
Is this possible?

 

[kingston via AccessMonster.com]

Create a temporary table, run an append query, and an update query:

TmpBook: BookID Title Author

Append the data from table Book to this table (or just duplicate Book and add
a field).

Create an update query with TmpBook and Author where BookID joins the two
tables. The output field is [TmpBook].[Author] and the Update To: block
should look something like:
IIF(IsNull([TmpBook].[Author]),[Author].[Author],[TmpBook].[Author] & ", " &
[Author].[Author])

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane