query to reference a previous record

[Guest]

Hello,

I am wanting to know how I can reference a previous record if a field in a
current record is blank. If there is no price for a particular date in my
table I would like it to return the previous days price

In the following example, I would like the price for the 03/10/2006 and
04/10/2006 to read 1.05 also

Date Price
01/10/2006 1.00
02/10/2006 1.05
03/10/2006
04/10/2006
05/10/2006 1.10

Thanks and regards,

Dan

 

[John Spencer]

This UNTESTED SQL statement might work for you

SELECT T3.Date,
IIF(T3.Price is not null, T3.Price,
(SELECT T2.Price
FROM YourTable as T2
WHERE T2.Date =
(SELECT Max(T1.Date)
FROM YourTable as T1
WHERE T1.Price is Not Null AND
T1.Date < T3.Date)) as ThePrice
FROM YourTable As T3

 

[Van T. Dinh]

Another variation:

SELECT T1.MyDate,
( SELECT T2.MyPrice
FROM Table1 AS T2
WHERE T2.MyDate =
( SELECT Max(T3.MyDate)
FROM Table1 AS T3
WHERE T3.MyDate <= T1.MyDate
AND T3.MyPrice Is Not Null )
) AS Price
FROM Table1 AS T1

(tested OK in A2003)