How to return records based on a calculated field

[John G]

Hi,
I have a query that returns the following records:

txtRecNumber RSum
8 3701
7 3030
6 30
5 0
4 769
3 0
2 1426
1 3800

"txtRecNumber" is the Record Number in Decending Order and "RSum" is a
calculated field. I want this query to return all the most recent records
which RSum is above the most recent "0". In this example it should return
3701, 3030 and 30. How do I edit my query to return just the records above
the most recent "0"? In other words, return records 6, 7 and 8 only.

Thanks in advance.

John G

 

[John Spencer]

SQL for that would look something like the following

SELECT TxtRecNumber, RSum
FROM YourQuery
WHERE txtRecNumber >
(SELECT Max(txtRecNumber)
FROM YourQuery
WHERE RSum = 0)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John G]

Thanks for this helpful information John Spencer. It works great!!

John G