Avatar of johnnyg123
johnnyg123
Flag for United States of America asked on

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

Avatar of undefined
Last Comment
johnnyg123

8/22/2022 - Mon
johnnyg123

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

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

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

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

Can you post your db by chance?
johnnyg123

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

ASKER
It's query1 that I copied
ASKER CERTIFIED SOLUTION
Michael Vasilevsky

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
johnnyg123

ASKER
Thanks very much...works great!