Concatenating values from a many relation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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])
 
Back
Top