Subform query

[Stevefield]

I have a database for entering details of books that have new editions published every year. The main form contains information about each book, e.g. in what section of the library it is kept. There is a subform for entering the publication date e.g. 2003, and the date it has been received.

I need to filter out all earlier edition details and get just the latest publication date and received date for each book to see which ones havent arrived. It doesnt matter what those dates are, I just need the last entry in the subform for each book. The subform table has variable numbers of entries for each book - unfortunately the book title is not included in the subform data - so i'm going to need to get that data from another table.

Do I need to use a macro here to get the latest entry for each ID Number? Or is there a very simple and easy way to do this?

This problem is driving me nuts! Any guidance/advice/help would be most welcome.

Steve.

 

[geoffcodd]

You should be able to use a query, group on the book title and use max on the publication date etc

Thanks
Geoff

PS. you will need to have the table with the book name included in your query

 

[Stevefield]

Thanks, that's brilliant! Took me 20 mins to learn about the 'totals/group by/max' stuff, and then to apply it to both 'edition' and 'received by' date.

I'd been delving into the help files as well as checking some books down in the lending section, but could not find what I needed.

Many thanks,

Steve