Selecting the latest row for each year (1 Viewer)

[Solved] Selecting the latest row for each year

Hi,

I'm trying to help my local school and I'm having trouble with a simple query. They have an access database with a table like this (simplified for this example):

--------------------------------
Name Result Date
--------------------------------
Bob 5 1/1/2014
Bob 6 2/2/2014*
Bob 7 3/3/2015
Bob 8 5/4/2015*
Len 5 1/1/2014
Len 6 2/2/2014*
Len 7 3/3/2015
Len 5 7/4/2015*
---------------------------------

Does anyone have any idea how I would write a query that gives me the latest result that each person earned in each year (to help I put a * symbol against the rows above that I am talking about)?

Bob 6 2/2/2014
Bob 8 5/4/2015
Len 6 2/2/2014
Len 5 7/4/2015

(The database is normalised and this table has an ID field, just in case anyone was wondering).

Any pointers in the right direction would be VERY welcome.

Des

Make a query like so

Now save that query as qryMaxResultPerYear and join this back on name and maxresultdate to Yourtable.

Thanks. It took me a few days to apply this to my real world example but I finally got there.

Your help was awesome. A small thing I figured out is I need the word 'AS' before the word ThisYear.

Also, to display the actual result, I needed to use a Last() function.

No the last function is far from reliable in this case....

I see I indeed omitted the as with the thisyear, my oppologies....

Once you save the query as qryMaxResultPerYear, you want to join this back into your table


That WILL return consistant aand proper results.

You are totally right. Thanks for your help. I think I have a whole new appreciation for how queries work thanks to you.

if you think this is something special about queries, you have a thing or two yet to go about queries....

They can be MAGIC!