Search Most Recent Record In Each Group of Records

[dtsparky]

I have numerous banana records each with a different date, numerous apple records each with a different date, numerous orange records each with a different date, etc.

How can I create a quick search that finds the most recent record for each catagory based on the date field, i.e. the most recent banana, most recent apple, etc.

Thank you

[mr_vodka]

You could use a subsummary reported grouped by category and sorted descending by date.

[dtsparky]

Yes,

I have that now. I was hoping to be able to create a search that would only show the most recent records in each catagory.

Thanks for your help anyway.

[AudioFreak]

It is possible you would just have to script a find for each category then go to the last record......Mark it....Then find the ones you marked. The end of the script would unmark the records so you don't find them later.

Could become really tedious though if you add a lot of different categories though.

HTH

[comment]

The easy way is to have a table of Categories, with each Category record showing data from the last related record in your current table.

Another option is to sort your records by Category and by Date (ascending). Then go to the first record, and omit the number of records in that category minus one. Move to the next record and repeat. Exit the loop when you land on the last record in the found set. However, this is a rather advanced technique that I wouldn't recommend to a beginner.

[bcooney]

The table of Categories also gives you the ability to have a CategoryID. Then if someone says, let's change Apple to McIntosh, you don't have to update the other table's records, since they have the correct CategoryID key stored.