Link to home
Start Free TrialLog in
Avatar of johnnyg123
johnnyg123Flag 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.  

Avatar of johnnyg123
Flag of United States of America image


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
Avatar of awking00
I'm not familiar with the datetime functions in access, but the attached works in Oracle. Perhaps someone can translate it for you.

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)
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
Can you post your db by chance?
Here is the test database
It's query1 that I copied
Avatar of Michael Vasilevsky
Michael Vasilevsky
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks very great!