Sub-Query - Where Clause

[SalisburyLad]

I would like to find out how to query records from 2 releted tables where the second table has a list of records that are date stamped but I only want to return the latest record with its corresponding data from the first table. In the attached DB I use 2 queries to acheive this but I need a SQL query that will run outside Access i.e. a SELECT query with a WHERE clause to replace:

SELECT tblBook.BookID, tblBook.BookName, tblBorrowedBy.BorrowedBy, tblBorrowedBy.BorrowedDate
FROM (tblBook INNER JOIN qryBorrowedLast ON tblBook.BookID = qryBorrowedLast.BookID) INNER JOIN tblBorrowedBy ON (qryBorrowedLast.MaxOfBorrowedDate = tblBorrowedBy.BorrowedDate) AND (tblBook.BookID = tblBorrowedBy.BookID);

and qryBorrowedLast:

SELECT tblBorrowedBy.BookID, Max(tblBorrowedBy.BorrowedDate) AS MaxOfBorrowedDate
FROM tblBorrowedBy
GROUP BY tblBorrowedBy.BookID;

I hope this makes sense.

TIA

 

[Kiwiman]

Howzit

Try this...

SELECT tblBook.BookID, tblBook.BookName, tblBorrowedBy.BorrowedBy, tblBorrowedBy.BorrowedDate
FROM tblBook INNER JOIN (tblBorrowedBy INNER JOIN (SELECT tblBorrowedBy.BookID, Max(tblBorrowedBy.BorrowedDate) AS MaxOfBorrowedDate
FROM tblBorrowedBy
GROUP BY tblBorrowedBy.BookID) AS T1
ON (tblBorrowedBy.BorrowedDate = T1.MaxOfBorrowedDate) AND (tblBorrowedBy.BookID = T1.BookID)) ON tblBook.BookID = tblBorrowedBy.BookID;

 

[SalisburyLad]

Made to look sooo simple! Thank you very much.