Select Query without duplicates

[anteagles20]

Hi,

I have a table of data with three fields:
Field 1 = reference number
Field 2 = year
Field 3 = grade

Reference numbers may be duplicated across different years but within each year the reference number will only appear once.

I want my select query to return each reference number with just the most recent year and corresponding grade i.e. if a reference number appears twice with 2012 and 2013 in the year field, I want the query to return just the 2013 record.

Is there anyway of doing this with just 1 query?

Thanks

[alansidman]

Suggest you upload a file with some dummy data for testing and analysis.

[anteagles20]

Here you go.

Thanks for the repsonse

[alansidman]

I think that I got what you are looking for with the use of two queries. Look at the attached.

Alan

[anteagles20]

Hi Alan,

Thanks for your help, I really appreciate it. Looking at your queries gave me an idea and I have actually managed to combine the elements of your queries into 1!

Essentially group by ref no, sort ascending examyear (so most recent exam year at the bottom) and last examyear and last grade.

I'm attaching the final query just in case anybody wants to take a look.

Thanks again Alan

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.