Calculated Field Help

[Alex Motilal]

Hello everybody.
I am developing a technical Database for a Textile Unit. The Unit runs in 3 shifts of 8 hours every day. 1st shift starts at 12 Midnight.
For Spinning Frames at the end of every shift Hank Meter Readings are taken. The difference between the consecutive Shift readings gives the Hank run for the Frame for a particular Shift ie., the 1st Shift and 2nd Shift readings give the Hank for the 2nd shift.
I have the following Tables:
1) tblSpinningFrames
Fields: frameID (PK), frame# (text), frameMake (text)
2) tblHankMeterReading
Fields: hankMtrReadingID (PK), frameID (ChildKey), shiftDate(Date),1stShiftReading, 2ndShiftReading, 3rdShiftReading (reading fields are number fields)
In the Query to calculate Hanks run for a particular Date, (2ndShiftReading-1stShiftReading) gives the Hanks run for the 2nd shift, and (3rdShiftReading-2ndShiftReading) gives the Hanks run for the 3rd shift. I am stuck up when calculating the Hanks run for the first shift, which is the difference between the previous days 3rd shift reading and the days 1st shift reading.
I need help immediately. The database has other tables also, which I feel, are not relevant to this subject.
With thanks in advance,
Alex

[weekend00]

if the shifts continue every day without weekend or holiday, use this query:

select hankMtrReadingID, frameID, shiftDate, [1stShiftReading], [2ndShiftReading], [3rdShiftReading], ([1stShiftReading] - dlookup("3rdShiftReading", "tblHankMeterReading", "shiftDate=#" & ([shiftDate]-1) & "#") as handrun1, ([2ndShiftReading] - [1stShiftReading]) as handrun2,([3rdShiftReading] - [2ndShiftReading]) as handrun3
from tblHankMeterReading

if the shifts don't continue every day,use this query(more slow):

select hankMtrReadingID, frameID, shiftDate, [1stShiftReading], [2ndShiftReading], [3rdShiftReading], ([1stShiftReading] - dlookup("3rdShiftReading", "tblHankMeterReading", "shiftDate=#" & dmax("shiftDate", "tblhankMeterReading", "shiftDate<#" & [shiftDate] & "#") & "#") as handrun1, ([2ndShiftReading] - [1stShiftReading]) as handrun2,([3rdShiftReading] - [2ndShiftReading]) as handrun3
from tblHankMeterReading

[Alex Motilal]

Thanks a lot Weedend00.
The solution worked nicely.
Alex