Filtering records in a query based on field values in adjacent records

[sdisalvo]

I'm looking for a way to filter records in a query (or report) based on
a value in a field of an adjacent record. Can this be done easily? I
cannot find any info in the help files.

Thanks

[Bob Quintal]

"sdisalvo" <sd******@earthlink.net> wrote in
news:11**********************@j55g2000cwa.googlegr oups.com:

I'm looking for a way to filter records in a query (or report)
based on a value in a field of an adjacent record. Can this be
done easily? I cannot find any info in the help files.

Thanks

Adjacent is ephemeral. What is the criteria that makes the records
adjacent? a date? a sequence number? If you have an autonumber for
example, the adjacent record will probably have the next higher or
lower number. When we know what makes a record adjacent, we may be
able to find a solution. But not before.
..
--
Bob Quintal

PA is y I've altered my email address.

[sdisalvo]

Sorry about that. There is an autonumber field in the table that would
work for me.

[Bob Quintal]

"sdisalvo" <sd******@earthlink.net> wrote in
news:11*********************@y43g2000cwc.googlegro ups.com:

Sorry about that. There is an autonumber field in the table
that would work for me.

You could create a query that uses two copies of your table with
the autonumber of the second instance filtered to be equal to the
first instances autonumber +1

untested, but should work.

SELECT a.* from mytable as a, mytable as b
WHERE b.autonum = a.autonum+1
and b.filterfield = "some value"
;
--
Bob Quintal

PA is y I've altered my email address.