Subquery - Return Last Record

[Jackson via AccessMonster.com]

Hi,

I'm trying to code a subquery on a table but to no avail...it's a very simple
table that has Date (short date, just recording the day) and NAV which is a
number (double).

All I'm wanting to do is have a query that displays the Date, the NAV and the
NAV from the day before - only recording business dates. In other words, the
subquery number I want is the NAV from the record preceeding the current one.
I've got as far as the below, but that just gets the same NAV number as
today's:

SELECT tblNAV.Date, tblNAV.NAV, (SELECT LAST(x.NAV) FROM tblNAV as X LEFT
JOIN tblNAV on x.Date=tblNAV.Date WHERE tblNAV.Date=x.Date) AS NAV2
FROM tblNAV;

Can anyone provide any guidance as to how I need to alter this?

Thanks.

 

[Douglas J. Steele]

"Last" is really a pretty useless concept in Access unless you have the
appropriate ORDER BY clause on the query. Far better would be to use Max.

 

[Jackson via AccessMonster.com]

Hi Doug,

In what context would I use Max - do I use max for selecting in the subquery
and on the join? What about the where?

"Last" is really a pretty useless concept in Access unless you have the
appropriate ORDER BY clause on the query. Far better would be to use Max.

Hi,

[quoted text clipped - 20 lines]

Thanks.

 

[John Spencer]

SELECT Nav
, tblNav.Date
, (SELECT N.Nav
FROM tblNav as N
WHERE N.Date =
(SELECT Max(Na.Date)
FROM tblNav as Na
WHERE Na.Date < tblNav.Date)) as PriorNav
FROM tblNav

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