Compare previous records in a query

  • Thread starter Thread starter Jasper Recto
  • Start date Start date
J

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
 
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?
 
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?
 
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
 
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
 
Back
Top