Extract two last recodes

[yosik20]

Extract two last recodes
Question regarding one to many relationship query
1. I have main table, lets say 'authors', and another table, lets say 'books'. The relationship is one-to-many (many books for one author – connected by author id).
2. I want to create a query (for a report) that extract the author and his last 2 books (referenced for example by book date).
How can I do it? (assuming there are authors with more or less books than 2)



Thanks all!

[Rixxe]

I think you might need something like:

SELECT TOP 2 AuthID, Author, Book, MAX(Date) as MaxDate
FROM Author AS b INNER JOIN Books AS a
ON a.AuthID = b.AuthID
WHERE Author = 'John Smith'
ORDER BY Book
GROUP BY AuthID, Author, Book

Somethings probably wrong or slightly off, so try it out and change at will.

Good Luck.

[yosik20]

thanks, Works Great!
One more thing - the Output now is tabular. How can I generate it as one line per author. where the 2 last book are in the same line...

[Rixxe]

You are welcome.

You want something like:

Author || Book1 || Book2

Erm, Oddly enough i've never had to do it, I do remember seeing someone last week on this site asking for the same thing, and getting a response.
I'll see if i can find it, if not google your heart out!

Good Luck.

[orange]

There is a concatenation function here, that may be hepful.

http://www.mvps.org/access/modules/mdl0004.htm