Concatenating values from a many relation

A

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?
 
K

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])
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top