Calculate Difference in Mileage Between Current and Previous Date?

[misscrf]

This question is related to post here:thread703-1632549 . That issue has been solved and now I have been thrown another request and a short deadline (grr).

Background - This is a database application to track expenses of cars in a fleet. The report I am developing shows costs by car (car number) and then by type of expense (car wash, gas, body work, oil change).

This report is set up to run from a form where the user can choose any filters that they want. They can choose 1 or more cars, and any date filter they want (month/year combo, year to date, > =, < = , between).

All of this works splendidly. The next challenge is that for the date range chosen, they want me to add the total mileage for that date range on the report. So If they do an all encompassing report - all dates, then each car would need for the cost type of gas, the gallons of gas, the cost, the mileage (difference), and the average miles / gallon.

The mileage (and gallons) is entered only when a gas cost type is entered.

So this is what it looks like:
intCarNum txtCarCostType DtCostDate intGallons CurCostAmount intMileage
4 Gas 1/12/2010 11.90 $34.60 41053
5 Gas 1/12/2010 6.70 $30.00 63918
7 Car Wash 1/12/2010 $5.39
10 Gas 1/12/2010 6.70 $30.00 40001
13 Gas 1/12/2010 8.20 $34.00 33202
15 Gas 1/12/2010 10.30 $31.00 57121
20 Gas 1/12/2010 7.00 $31.00 26960
1 Gas 1/13/2010 13.40 $39.00 3123
2 Car Wash 1/13/2010 $5.39
4 Gas 1/23/2010 8.60 $35.00 49331
8 Gas 1/25/2010 5.20 $25.00 24123
9 Gas 1/27/2010 6.90 $30.00 40805
12 Gas 1/13/2010 8.40 $35.00 61851
13 Car Wash 1/13/2010 $5.39
13 Gas 1/13/2010 5.10 $25.00 33339
15 Repair & Maintenance 1/13/2010 $25.92
5 Gas 2/10/2010 5.00 $35.00 63998
6 Gas 2/14/2010 13.10 $48.00 63557
10 Gas 2/14/2010 6.70 $30.00 40193
17 Gas 2/14/2010 7.50 $32.00 57317

What I need is to line up for each car, if the record is a "gas" record, the difference for the mileage in that record to the mileage in the previous record, going by date. There may be some on the same date (I found at least one of those), so we need to check for date to be equal and then in that case check to see if the other mileage on that day is less or more. If less, than difference, if more than difference of next previous date.

These dates will not be consecutive or consistent at all.

This is the main query that I have:

SELECT tblCar.PKCarID, tblCarNum.intCarNum, tblCarStatus.txtCarStatus, tblCarMake.txtCarMake, 
tblCarModel.txtCarModel, tblCarCostType.txtCarCostType, tblCarCost.DtCostDate, tblCarCost.intGallons, 
tblCarCost.CurCostAmount, tblCarCost.intMileage, tblCarCost.PKCarCostID, tblCarCost.FKCostType

FROM (tblCarStatus RIGHT JOIN (tblCarNum RIGHT JOIN (tblCarModel RIGHT JOIN 
(tblCarMake RIGHT JOIN tblCar ON tblCarMake.PKCarMakeID = tblCar.FKCarMake) 
ON tblCarModel.PKCarModelID = tblCar.FKCarModel) 
ON tblCarNum.PKCarNumID = tblCar.FKCarNum) 
ON tblCarStatus.PKCarStatusID = tblCar.FKCarStatus) 
LEFT JOIN (tblCarCostType RIGHT JOIN tblCarCost 
ON tblCarCostType.PKCarCostTypeID = tblCarCost.FKCostType) 
ON tblCar.PKCarID = tblCarCost.FKCar

GROUP BY tblCar.PKCarID, tblCarNum.intCarNum, tblCarStatus.txtCarStatus, tblCarMake.txtCarMake, 
tblCarModel.txtCarModel, tblCarCostType.txtCarCostType, tblCarCost.DtCostDate, tblCarCost.intGallons, 
tblCarCost.CurCostAmount, tblCarCost.intMileage, tblCarCost.PKCarCostID, tblCarCost.FKCostType;

is this something that is possible to do? I have been looking for hours and none of the posts on this site or others discuss this actual problem. They are similar but the differences make them unapplicable.

Thank you for any help!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

so I haven't gotten any responses to this. That usually means I am not explaining this well. Can anyone let me know what is lacking or confusing in my post please? I could really use some help.

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

Say the data sample you posted is named tblCarCosts.
Here an example of how to calculate the difference for the mileage for each "Gas" record having a predecessor:

SELECT C.intCarNum,C.txtCarCostType,C.DtCostDate,C.intGallons,C.CurCostAmount,C.intMileage
,C.intMileage-MAX(G.intMileage) AS DiffMileage
FROM tblCarCosts AS C LEFT JOIN (
SELECT intCarNum,txtCarCostType,intMileage FROM tblCarCosts WHERE txtCarCostType='Gas'
) G ON C.intCarNum=G.intCarNum AND C.txtCarCostType=G.txtCarCostType AND C.intMileage>G.intMileage
GROUP BY C.DtCostDate,C.intCarNum,C.txtCarCostType,C.intGallons,C.CurCostAmount,C.intMileage

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886