SQL statement to choose most recent date

[Alder]

This is a simplified version of what im trying to do so
lets say I have two fields I am using. It's the concept that I cannot figure out.

Here are 2 fields I am using:

fldBookTitle = Title of the book
fldDateCheckedOut = Date book was checked out

There are multiple instances of each book being checked
out. For example, Peter Pan has been checked out 3 times,
Lord of the Rings was checked out 4 times. Each time a
book was checked out, the date of check-out was recorded.

What I want is for every book I have on my table, I
only want the latest record displayed in the query.

Example of data before query:

Peter Pan | 3/7/97
Peter Pan | 4/3/97
Peter Pan | 5/20/97
Lord of the Rings | 3/20/97
Lord of the Rings | 4/20/97
Lord of the Rings | 5/20/97
Lord of the Rings | 6/20/97

Result of Query:

Peter Pan | 5/20/97
Lord of the Rings | 6/20/97


Help with this would be greatly appreciated, Thank you.

[AdrianH]

There's probably a better way, but what I thought was if you run a select query for each Book and order by Date descending, then you will have the latest Date as the first record, eg.

"Select * from [YourTable] where fldBookTitle = "Peter Pan" order by fldDateCheckedOut Desc"

The first record returned has the latest date.

Theres probably an SQL statement guru in the forum who could do this far more efficiently.

[Alder]

The table has multiple books, each of which show up many
times. Such as 3 Peter Pan books and 4 Lord of the Ring
books. In the resulting query, I want one query which
displays a list of one of each book. Each book chosen
out of the table having the latest date compared to
other books of the same title.

Table:

Peter Pan | 3/3/77
Peter Pan | 3/4/77
Peter Pan | 3/4/80
Lord of the Rings | 3/4/99
Lord of the Rings | 3/5/99
Lord of the Rings | 3/6/99
Lord of the Rings | 3/7/99

The query results would show one of each book that has the
latest date:

Peter Pan | 3/4/80
Lord of the Rings | 3/7/99


I cannot figure out how to make a query that would
show these results from this table. Is it even possible?

[Alder]

Sorry about that AdrianH. It took a while for what you said
to set in. I had not thought about that solution. It may
help me think of a more efficient way. Thank you :)

[theroper]

SELECT [Title],MAX([Date]) as Date
from [Your table]
Group By [Title]

There you go that should do it!