Conditionally placing same field into different output fields

[Guest]

Hoping for some help here. I have two tables in my database that I'm trying
to join. A simplified example

TABLE1
Article Year
1 1995
2 1996


TABLE2
Article Author Position
1 Jones 1
2 Smith 1
2 Doe 2
2 Green 3

So, article 1 was just by Jones. Article 2 was by Smith, Doe and Green--in
that order.

I'd like to write a query to produce

ARTICLE YEAR AUTHOR_1 AUTHOR_2 AUTHOR_3
1 1995 Jones
2 1996 Smith Doe Green

I've tried about 17 million different approaches and am stumped. Can anyone
help! Thanks.

 

[John Spencer]

This looks like a crosstab query to me.

TRANSFORM First(Author) as AuthorName
SELECT A.Article, A.Year
FROM Table1 As A INNER JOIN Table2 as B
ON A.Article = B.Article
GROUP BY A.Article, A.Year
PIVOT Position

 

[Guest]

Use two queries like this --

SELECT ConfusedTable1.Article, ConfusedTable1.Year, ConfusedTable2.Author,
ConfusedTable2.Position
FROM ConfusedTable1 INNER JOIN ConfusedTable2 ON ConfusedTable1.Article =
ConfusedTable2.Article;

TRANSFORM First(Confused.Author) AS FirstOfAuthor
SELECT Confused.Article, Confused.Year
FROM Confused
GROUP BY Confused.Article, Confused.Year
PIVOT "Author_" & [Position];