Previous record in an expression

[GD]

What expression would I use in a query to refer to the table's previous
record if the current record is blank?

In other words, hypothetically, I have Field A (from TableA) & Field B (new
query field) below. Where there are gaps in Field A, I want Field B to show
the previous record's number. Example:

A B
1 1
2 2
2
2
3 3
3
4 4
5 5
5
5
5
6 6
6

 

[Michel Walsh]

A TABLE (in SQL) has no previous record. Unless some recordsets which
maintain an initial list of membership, a table is just a bag of records and
like marbles in a bag of marbles, these records can move around, relatively,
on their physical position on the hard disk.


All is not lost, though, if you are a FIELD which indicates the order. Such
a field can be a date_time of transactions, or an increasing integer, or
whatever similar where the 'previous' record is then the record having the
greatest value for that field, which is less than the actual value: (in SQL
view)


SELECT y.A, y.B, ( SELECT MAX(x.A)
FROM myTable AS x
WHERE x.C <= y.C) AS previousAvalue
FROM myTable AS y



with values like:


C A B
1 1 1
2 2 2
3 2
4 2
6 3 3
8 3
9 4 4
10 5 5
11 5
12 5
16 5
17 5
18 6 6
19 6



Note that field C indicates the order to consider.


Instead of a sub-query, you can use a join, instead of a sub-query:

SELECT y.C, LAST(y.A), LAST(y.B), MAX(x.A) AS previousAvalue
FROM myTable AS x INNER JOIN myTable AS y
ON x.C <= y.C
GROUP BY y.C



and, to replace the nulls under column A, someone can use Nz:

SELECT y.C, LAST(y.A), LAST(y.B), Nz( LAST(y.A), MAX(x.A) )
FROM myTable AS x INNER JOIN myTable AS y
ON x.C <= y.C
GROUP BY y.C





Vanderghast, Access MVP