comparing values with previous record

Question

how do i compare a value in a record with a value in the previous record in the same table?

Answer 1

Sure, but you'll need to elaborate on the nature of the comparison.

Short answer: you can create a one-field subquery by typing something like

PreviousX: (SELECT X.Somefield FROM yourtable AS X WHERE X.datefield = (SELECT Max(Y.Datefield) FROM yourtable AS Y WHERE Y.datefield < yourtable.datefield))

or

PreviousX: (SELECT TOP 1 X.Somefield FROM yourtable AS X WHERE X.datefield < yourtable.datefield ORDER BY X.datefield)

Answer 2

Thanks John.  We have one table with an autonumber primary key field.  The field we want to compare is a date time field.  The records are in date time ascending sequence.  Could you elaborate re. the subquery or non-equi join process?  Thanks.

Answer 3

You'll need some way to define "previous". Access tables (and all proper relational tables!) have no defined order; they should be viewed as an unordered "bag" of records.

You will need some field or combination of fields in the table to specify the sort order that's meaningful in your case. This could be a sequential Autonumber, a timestamp, perhaps some other fields appropriate for your case.

You can then use a subquery or a non-equi join to bring the two records together for comparison.