Latest record by date

[Manos Del Fuego]

I am trying to create a query or series of queries that will identify the latest record of a field called CheckInDate. The table will have multiple entries. For example.

Book#__________CheckOutDate_____________CheckInDate
__1_____________01/01/2007_______________03/01/2007
__1_____________04/01/2007_______________05/01/2007
__1_____________01/01/2007_______________
__2_____________01/01/2007_______________02/01/2007
__2_____________02/05/2007_______________04/27/2007
__3_____________01/01/2007_______________03/01/2007
__3_____________05/01/2007_______________07/01/2007

I need the results to be:
#1(ALL) will not be listed, as there is a book still checked out
#2 will show the record with the 04/27/2007(ONLY), as I want the last time the book was checked out.
#3 will show the record with the 07/01/2007(ONLY), as I want the last time the book was checked out.

I currently have a somewhat flat table where this is concerned. Please advise on what I would need to do to create this. Thanks!

 

[Jon K]

Try these two queries, using the correct table name.

qryOne:-
SELECT [Book#]
FROM [TableName]
WHERE [CheckInDate] Is Null;

qryTwo:-
SELECT [TableName].[Book#], Max([CheckInDate]) AS LatestCheckInDate
FROM [TableName] LEFT JOIN qryOne ON [TableName].[Book#] = qryOne.[Book#]
WHERE qryOne.[Book#] Is Null
GROUP BY [TableName].[Book#];


Run the second query.
.