query to return last non zero value

Below is some sample table data (table name is scores)

Month Year Score
------- ----- -------
Nov 2009 100
Dec 2009 200
Jan 2010 0
Feb 2010 0

I am trying to write a query that will return the month, year score of the last entry that has a non zero score.

Microsoft AccessSQL

Last Comment

johnnyg123

8/22/2022 - Mon

johnnyg123

2/19/2010

ASKER

Forgot to mention that in the example above I would like to retrieve

Dec 2009 200 record

Should also mention that once the non zero score value is found all entries below it will all have zero scores

awking00

2/19/2010

I'm not familiar with the datetime functions in access, but the attached works in Oracle. Perhaps someone can translate it for you.

query.txt

Michael Vasilevsky

2/19/2010

How about:

SELECT [Month], [Year], [SCORE]
FROM MyTable
WHERE [Month] & [Year] IN (SELECT [Month] & [Year] AS ID, Max(FormatDateTime([Month] & "/1/" & [Year]) FROM MyTable WHERE [SCORE]<>0)

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

johnnyg123

2/19/2010

ASKER

I tried

SELECT [Month], [Year], [SCORE]
FROM scores
WHERE [Month] & [Year] IN (SELECT [Month] & [Year] AS ID, Max(FormatDateTime([Month] & "/1/" & [Year])) FROM scores WHERE [SCORE]<>0)

but got the dreaded

You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause
Revise the SELECT staement of the subquery to request only one field

Michael Vasilevsky

2/19/2010

Can you post your db by chance?

johnnyg123

2/19/2010

ASKER

Here is the test database
retrieve-blank.mdb

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

johnnyg123

2/19/2010