What should be the query in MS Access 2007

Question

There is one table with coulmns :
id,bookid,name,dateofentry,status.

Need result where bookid and last dateofentry for corresponding book and status of book i.e R only (R- Return, and NR- Not return)

for e.g. input :-

id bookid subject dateofentry Status
1   10      math   10-11-2012 NR
2   10      math   1-12-2012  R
3   110     math   1-12-2012  NR
4   110     math   10-12-2012 NR
5   102     math   10-11-2012 NR
6   102     math   1-12-2012  R
7   105     math   10-12-2012 NR
8   105     math   17-12-2012 NR
9   106     math   11-12-2012 NR
10  106     math   14-12-2012 R

output:-

10   math 1-12-2012 R
102  math 1-12-2012 R
106  math 14-12-2012 R

What should be the query for this

Thanks in Advance

I tried this:-

SELECT t.bookid, t.satus, r.MaxDate
FROM (SELECT bookid, MAX(dateofentry) as MaxDate
      FROM TempLogs
      GROUP BY bookid) r
INNER JOIN Logs t ON t.bookid = r.bookid AND t.dateofentry = r.MaxDate where status="R" 

But I am getting some syntax error, and its not working.

Answer 1

It looks like the syntax error is because you're using 2 different table names. In your subquery, you are using table TempLogs but in the outer query, you refer to table Logs. Apart from that the actual query and methodology for what you're trying to achieve appears correct.