Query - show only recent record of same action

[flyer26]

All right, here is my problem. I willl appreciate any help. Pardon me for my grammatical errors. English is no my first language.

So, problem is in constructing of one Query in Access database. I create database for borrowing something like books.
I have a table named evidencija where are recorded all actions. That table looks like this:

See IMG01 in ZIP attachment.

ID is auto-number(primary key of this table). Field Područje is relatively connected field from another table. Područje are something like books that should be borrowed. Prezime is also relatively connected field from another table. Prezime on english means "second name, surname". This represents all members who can borrow books. So, this table record all state changes. Datum posudbe and Datum povrata are date of take/date of return of book.
But, what I need?

I want to make a request that will put out all the books that have been restored! BUT, i don't want that book that has been borrowed twice (ex. Book 10)come twice in result (ID17& ID18). I want that Query show me only last change (only ID18). ID17 should not come out because there is recent record for this book borrowing. Also, books that are not returned should not apear in result of query.
So, final result of query should look like this:

See, IMG02 in ZIP attachment.


Explanation: IDs: 17, 19, 21 and 24 should not apear because there are recent record of this borrowings. ID20 should not apear because this book has no date of return (it's not returned yet).

Has anyone an idea how to do this? If you need any more informations, feel free to ask. I tried to explain best I can on english.
Tnq very much!

 

[pbaldy]

Is this what you want?

http://www.baldyweb.com/LastValue.htm

 

[flyer26]

It's looking like that should be that. But I can't test it now because in my country now is almost midnight so I'm really tired.
But I will test tomorrow and feedback you.
I really hope that you found what I need so much...
Tnq!

 

[pbaldy]

Sleep well, and welcome to the site!

 

[bparkinson]

If the most recent record has a date column, you can use the TOP keyword with an ORDER BY to select just the most recent record in one SQL Statement, like this:

Table:
ID
ReturnDate
BookName

For this table, if the same bookname was returned sever times on several different dates, to get just the most recent return, this is the SQL

SELECT TOP 1 * FROM Table ORDER BY ReturnDate DESC

 

[pbaldy]

That would not return the last record for each book, just the last record.

 

[bparkinson]

You are correct. Make it a correlated subquery with a WHERE clause relating it to each book. Also, there need to be an ISDATE clause in there to exclude rows with no dates.

 

[flyer26]

TNQ!

Pbaldy, that is what I need!
I tested it and it's working!

Thank you

Bparkinson, tnq you too. Yeah, I tried few days ago something similliar like your idea but it didn't work. Tnq, anyway...

This is forum that every MySQL programer had to now. I'll tell about this site to my colleagues. Really helpful site...
Tnx, one more time

 

[pbaldy]

I'm glad it helped you. Welcome to the site by the way!