Pick the newest date

[KLP]

I have a table of financial statements, one of the fields is a financial
statement date. There is a report that shows the data from each year
(record). I want to create a report that picks the record of the most
current year.

Any ideas on how to accomplish this would be appreciated.

Thank you,
Kelvin

 

[Ofer Cohen]

Using a sub query

Select T1.* From [financial statements] As T1 Where
T1.[financial statement date] In (Select Top 1 T2.[financial statement date]
From [financial statements] As T2 Where T2![financial Id] = T1![financial Id]
Order By T2.[financial statement date] Desc)

Note:
change the "financial Id" field to the field name that distinguish each
group of dates

The Top 1 specify that I want only the highst date to return, later if
you'll want the last 2 recent dates then change it to
TOP 2