My goal today is to replicate a running count of a row based on the ID. This is what I have so far.
Code:
SELECT (Select COUNT(Equipment_EquipmentID)
FROM Equipment_OdometerHistory as eoh2
WHERE eoh2.Equipment_EquipmentID = eoh.Equipment_EquipmentID
GROUP BY Equipment_EquipmentID) as Transactions,
eoh.Equipment_EquipmentID, e.AccountingCode, eoh.ReadingDate, eoh.TrueReading,
(SELECT TOP 1 LAG.TrueReading from Equipment_OdometerHistory as LAG
WHERE (LAG.Equipment_EquipmentID = eoh.Equipment_EquipmentID) and (LAG.TrueReading < eoh.TrueReading)
GROUP BY LAG.Equipment_EquipmentID, LAG.TrueReading
ORDER BY LAG.TrueReading DESC, LAG.Equipment_EquipmentID
) AS Prev_Value,
(eoh.TrueReading-Prev_Value) AS [Elapsed Odometer]
FROM Equipment AS e INNER JOIN Equipment_OdometerHistory AS eoh ON e.EquipmentID = eoh.Equipment_EquipmentID
WHERE (((e.IsDeleted)=False))
GROUP BY eoh.Equipment_EquipmentID, e.AccountingCode, eoh.ReadingDate, eoh.TrueReading;
Here is the current result set:
Here is the desired result set (I can get it to work easily in SSMS with the OVER clause and I feel like I am really close in Access, but just can't seem to figure it out):