Query that returns only one record for each unique value in a spesific field

[Hallgeir]

I have a table like this:
AutonrId Country Date
1 Norway 2006-05-15
2 Finland 2006-05-20
3 Norway 2006-05-22
4 Sweden 2006-05-01
5 Sweden 2006-05-15
6 Finland 2006-05-15
etc

I want to build a query that returns only the latest record on each country.
Like this:
2 Finland 2006-05-20
3 Norway 2006-05-22
5 Sweden 2006-05-15

Is this possible and if it is, how can it be done?
I appreciate any suggestions that could help me.

 

[Ofer Cohen]

In a query you can try this criteria

Select * From TableName Where [Date] =
DMax("[Date]","[TableName]","[Country] = '" & [Country] & "'")

If you have a field named Date, I would advice you changing tis field name,
it is not recomnded using key words in Access as fields names.

 

[John Spencer]

In the SQL view, your query would look like the following

SELECT AutonrId, Country, [Date]
FROM YourTable
WHERE [Date] =
(SELECT Max(Temp.[Date]
FROM YourTable as Temp
WHERE Temp.Country = YourTable.Country)

If you are using the grid,
Field: Date
Criteria: =(SELECT Max(Temp.[Date] FROM YourTable as Temp WHERE Temp.Country = YourTable.Country)