Compare previous records in a query

[Jasper Recto]

Is it possible to compare a previous record in a query?

I have a query that has 4 columns that give me the date, part, operation and
Opvalue

In a 5th column, I want to compare each line to the previous line, if the
first 3 criteria's match (Date, Part and Operation) I want the 5 column to
be 0, if not, I want it to be the OpValue from column 4.


Is this possible in a query?

Thanks,
Jasper

 

[KARL DEWEY]

To do this kind of comparrison you need something that sets the order of the
records to know which is first.
Do you have an autonumber ID field?

 

[Jasper Recto]

No I don't. Could I add one just for the query?

Thanks,
Jasper

 

[KARL DEWEY]

No.

Records in a table are like a barrel of brick, in no set order and therefore
may not display the same all the time without something to sort on.

Do you have a field to sort on that will result in the order you want the
records?

 

[Jasper Recto]

The fields are sorted by the date and then the part and then op. The sort
works well and the fields should be in the desired order.

Jasper

 

[KARL DEWEY]

Ok, then post a sample dataset.

 

[Jasper Recto]

PayrollDate PartNum OprSeq LaborHrs Week Of EarnedHrs
03/20/2008 10862868 10 2.7 03/16/2008 2.7
03/24/2008 10862868 20 1.57 03/23/2008 0
03/20/2008 10862868 20 4.33 03/16/2008 3.5
03/25/2008 10862868 30 9.93 03/23/2008 6
03/27/2008 10862868 40 2.1 03/23/2008 0
03/27/2008 10862868 40 5.57 03/23/2008 2
05/12/2008 10866123 50 1.83 05/11/2008 0
05/12/2008 10866123 50 8.75 05/11/2008 0
04/22/2008 10875398 40 2.6 04/20/2008 2
04/27/2008 10875398 40 1.5 04/27/2008 0
04/18/2008 10918159 20 3.03 04/13/2008 0
04/12/2008 10918159 20 3.29 04/06/2008 0
03/18/2008 10918159 20 3.48 03/16/2008 0
03/18/2008 10918159 20 0.48 03/16/2008 0
04/21/2008 10920638 40 2.9 04/20/2008 2.9




Karl, above is a sample data set. It is sorted by part #, OprSeq and then
date.

I would like to compare the each line to the previous entry. If they are
the same Part # and Op then I need to compare the dates to determin what the
date difference is.

Any ideas?
Thanks,
Jasper

 

[Jasper Recto]

Sorry,

The same data set looked alot better when I posted it.
Take a look below