Trying to get difference in Meter readings between rows

[Mike S.]

I have an equipment repair table called Repair_History.
Each row represents a work order for a particular piece
of equipment. Each row has several columns including
Equipment#, WorkOrder#, Meter and WO_Date. For each
equipment number in the table I need get the interval
meter readings from one work order to next. So I need a
query sorted by equipment that will give the following
fields:
Equipment #, Current_Workorder, Current_Workorder_Date,
Current_Workorder_Meter, Previous_Workorder_Meter,
Meter_Difference.

The first occurance for each Equipment # would have a
blank in it's "Previous_Workorder_Meter" field since it
is the first workorder for that equipment and there is no
previous.

I cannot get Access to pick up the correct previous meter
in my queries. Can anyone help!
Thanks,
Mike

 

[fredg]

I have an equipment repair table called Repair_History.
Each row represents a work order for a particular piece
of equipment. Each row has several columns including
Equipment#, WorkOrder#, Meter and WO_Date. For each
equipment number in the table I need get the interval
meter readings from one work order to next. So I need a
query sorted by equipment that will give the following
fields:
Equipment #, Current_Workorder, Current_Workorder_Date,
Current_Workorder_Meter, Previous_Workorder_Meter,
Meter_Difference.

The first occurance for each Equipment # would have a
blank in it's "Previous_Workorder_Meter" field since it
is the first workorder for that equipment and there is no
previous.

I cannot get Access to pick up the correct previous meter
in my queries. Can anyone help!
Thanks,
Mike

Here is a similar query that computes miles driven by a fleet of cars.
I'm sure you can adapt it to your needs.

SELECT tblMultiCars.Auto, tblMultiCars.dteDate, tblMultiCars.Mileage,
Abs([Mileage]-DLookUp("Nz([Mileage])","tblMultiCars","[Auto] = '" &
[Auto] & "' and Month([dteDate]) = " & Month([dteDate])+1)) AS
MilesTraveled
FROM tblMultiCars
ORDER BY tblMultiCars.Auto, tblMultiCars.dteDate DESC;


Change the table name to your table name.
Auto should be your Equipment# field (Text datatype), Mileage should
be your Meter reading field, and dteDate is the WorkOrder data..
Change the Order By to sort differently if desired.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


The solution is in this MS KB article:

http://support.microsoft.com:80/support/kb/articles/q101/0/81.asp

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQMX/34echKqOuFEgEQKD1gCfdBWQsMfuF/Ku6Y2riJrd8kQjTYUAoKpd
l5DIHAGLOVcpaPMeesc3ypo8
=IaKb
-----END PGP SIGNATURE-----