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.

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)

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

johnnyg123

ASKER

It's query1 that I copied

ASKER CERTIFIED SOLUTION

Michael Vasilevsky

membership

This solution is only available to members.