If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trying to get difference in Meter readings between rows
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 |
#2
|
|||
|
|||
Trying to get difference in Meter readings between rows
On Tue, 8 Jun 2004 09:52:15 -0700, Mike S. wrote:
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. -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
#3
|
|||
|
|||
Trying to get difference in Meter readings between rows
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 The solution is in this MS KB article: http://support.microsoft.com:80/supp.../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----- Mike S. wrote: 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 |
Thread Tools | |
Display Modes | |
|
|