query and how to show muliple results on one row

[joeparts]

Hi,

I have a relational database in Access of scientific articles that
contains multiple tables, like: article / authors / etc. The articles
and Authors are linked via an ArticleAuthor table that contains the id
of the article and the id(s) of the author(s) involved in the article.

If I do a query to show the basic characteristics of an article, like
who wrote it, when it was published etc, it results in an overview
whereby the authors are listed below each other. Instead I want the
complete information to stay in one row.

See the snapshot below:
articleId name date authorId
14 Holak, S.L. 1988 7
17 Andrus, R. 1970 115
17 Poulsen, L. 1970 116
18 Corey, L.G. 1971 11
19 Jacoby, J. 1971 12

Clearly article ID = 17 should show the authors on one line.

My question is how to do so. How do I tell access using SQL code that
it should list the authors 'next' to each other.

The code i use for now is as follows:

SELECT articleAuthor.articleId, authors.name, articles.date,
articleAuthor.authorId
FROM articles INNER JOIN (authors INNER JOIN articleAuthor ON
authors.ID = articleAuthor.authorId) ON articles.id =
articleAuthor.articleId;

Any help would be greatly appreciated!

Thanks.
Joep

 

[Joan Wild]

At www.mvps.org/access look for
Return a concatenated list of sub-record values and Concatenate fields in
the same table in the Modules section.