most current record of group

[Howard]

I trying to find a simple way of pulling out the most
current record in a group. Example: every year a company
submits a report. After a few years there are multiple
reports from that one company. I want to extract the most
current report out. Is there a function for this? Thanks!

 

[Arvin Meyer]

You need to store a date/time with the data if you want the most recent
data. Then you can sort descending and use the TOP predicate (look up TOP in
the help files) to get the most recent record(s).
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Allen Browne]

A subquery might be the easiest way to achieve this.

This example assumes you have tables named tblCompany (the companies) and
tblReport (the reports). Create a query into just tblCompany. Type this into
the Field row:
LastReportID: (SELECT TOP 1 ReportID FROM tblReport
WHERE tblReport.CompanyID = tblCompany.CompanyID
ORDER BY tblReport.ReportDate DESC, tblReport, ReportID)

Save the query. You can now create another query based on this one and
tblReport to get full details of the latest report for each company.