First record of each year

[simon03]

Hi

How can I select the first record of each year working with a table like this:

ID, value, date
0, 30, 01/01/2000
1, 40, 03/02/2000
2, 20, 10/03/2000
3, 10, 02/05/2001
4, 20, 09/08/2001
5, 10, 01/02/2001

I'd like to get this result from my Query:

30, 01/01/2000
10, 01/02/2001

I've been searching for a possible solution in this forum and in other websites but could not find anything that pointed me in the right direction.

Thanks!

 

[jdraw]

Try this general approach.

Note: I changed your field names because Value and Date are reserved words in Access.

Select mID
,mValue
,mDate from myTest 
where mdate in 
(SELECT Min(mytest.mDate) AS MinOfmDate
FROM mytest
GROUP BY Year(mdate));

 

[simon03]

Thanks that was helpful