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

  • Thread starter Thread starter Hallgeir
  • Start date Start date
H

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.
 
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.
 
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)
 
Back
Top