Finding a previous line

E

Emmweb

I have a table with a bunch of currency fields and an autonumber primary key.
I have a query which takes the ID of the record being currently used in the
form, subtracts one (to get the line before) and then gives the value of a
particular field, showing me the value of the a field in the record previous.
However, this obviously only works if no ID numbers have been skipped, if no
line has been deleted. Can anyone help me with the formula I'd use or method
to get the value of the field in the record prior, even if the ID number of
that prior record isn't current ID-1?

TIA,
Emma
 
R

Rick Brandt

Emmweb said:
I have a table with a bunch of currency fields and an autonumber
primary key. I have a query which takes the ID of the record being
currently used in the form, subtracts one (to get the line before)
and then gives the value of a particular field, showing me the value
of the a field in the record previous. However, this obviously only
works if no ID numbers have been skipped, if no line has been
deleted. Can anyone help me with the formula I'd use or method to get
the value of the field in the record prior, even if the ID number of
that prior record isn't current ID-1?

TIA,
Emma
Click to expand...

Do a SELECT TOP 1 query sorting in descending order on the ID field with a
criteria of < ID on the form.
 
E

Emmweb

Thanks so much! Works perfectly!
Erin

Rick Brandt said:
Do a SELECT TOP 1 query sorting in descending order on the ID field with a
criteria of < ID on the form.
Click to expand...
 
E

Emmweb

Ok, so now let's say I want to show *each* line's previous line's field, not
just the previous line's field for just the one record. Any reasonable way to
go about that?
 
Top