Today Value minus Previous Value in Query

[aStoerm]

Hello--

I am working on a database for a transportation company. They have an issue with their trucks being down and out of service which is where my problem begins.

I need to take the MaxOdometer from a trucks previous day in operation (PreviousMaxOdometer) and subtract it from todays MaxOdometer (TodayMaxOdometer). Here is an example of my data:

TruckID | TransDate | Odometer

T1800 | 6/25/12 | 89000
T1806 | 6/25/12 | 99000
T1806 | 6/25/12 | 98700
T1800 | 6/23/12 | 88300
T1806 | 6/22/12 | 98000
T1800 | 6/15/12 | 87000
T1806 | 6/13/12 | 97900

The data should give a (MaxPreviousOdometer) by date so that I can find MPG and such. It also solves the amount of fuel used during a day issue for me. Any help would be greatly appreciated.

Thanks for the help,

aStoerm

 

[pbaldy]

I have a method on my site but Allen's is more efficient:

http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[aStoerm]

pbaldy-

I am having trouble with his code here is what I have so far:

.DriverID, [All Activity].TransDate, [All Activity].Odometer,
(SELECT Top 1 Dupe.Odometer
FROM [All Activity] AS Dupe
WHERE Dupe.DriverID = [All Activity].DriverID
AND Dupe.TransDate < [Today Max Milage].TransDate
ORDER BY Dupe.TransDate DESC, Dupe.DriverID)
FROM [All Activity]
GROUP BY [All Activity].DriverID, DLookUp([TransDate],[All Activity],"[TransDate]=" & [TransDate]-1)
HAVING ((([All Activity].DriverID) Like "R****"));

I am green as far as SQL goes, I basically quilt the code together from forums and such so when I get a syntax error I get lost easily.

I am getting "TransDate cannot be used in an Aggregate" error.

Thanks for your help, btw.

A

 

[pbaldy]

That doesn't look complete, and what's the purpose of the DLookup()?

 

[aStoerm]

I got lost and that code was confusing me, it is based on the link you sent me. I decided to start using "last" but that wasn't working, it was just giving me random previous data. So I started to use DMax() and pulling the data that way and it seems to be working better, I am just trying to figure out the syntax but this seems the way to go. Just working on the DMax criteria.

 

[gemma-the-husky]

the other way of doing it is not to worry so much about sequential readings

if you want to calculate mpg over a month, all you need is a query that extracts all the fills for a month.

take the low odo, the high odo, and divide by the total fuel.

you may need to make it slightly more sophisticated as this idea may miss a fill.


i use this technique to summarise fills, sort by mpg to give a sorted "league table" of vehicles by mps. etc etc


-----
this idea even works for just one fill. but it's a different (and more relational way) of thinking about your data.

it's getting away from the "spreadsheet" thinking of needing to track the before/after odo readings in sequence. it just is not needed in those terms. the trick is to think about the whole set of data you are dealing with, and try and construct queries that give you the results.


you may need some code to check that the odos do run in ascending order, and there is no rogue data, but that is a slightly different matter.

 

[plog]

You are going to need a sub query (well not really 'need', but its the path of least resistance), so copy the below code into a query and save it as a query called 'DailyOdometer_sub':

SELECT YourTableNameHere.TruckID, YourTableNameHere.TransDate, Max(YourTableNameHere.Odometer) AS MaxOdometer
FROM YourTableNameHere
GROUP BY YourTableNameHere.TruckID, YourTableNameHere.TransDate;

In the above code, be sure to replace all instances of 'YourTableNameHere' with the name of your actual table. Once done, use the below SQL to generate the actual data you want:

SELECT DailyOdometer_sub.TruckID, DailyOdometer_sub.TransDate, DailyOdometer_sub.MaxOdometer, DMax("[TransDate]","[DailyOdometer_sub]","[TruckID]='" & [TruckID] & "' AND [TransDate]<#" & [TransDate] & "#") AS PriorMaxOdometerDate, DMax("[MaxOdometer]","[DailyOdometer_sub]","[TruckID]='" & [TruckID] & "' AND [TransDate]<#" & [TransDate] & "#") AS PriorMaxOdometer
FROM DailyOdometer_sub;

Run that and it should give you the data you want.

 

[MStef]

I think it can be done via Module (function).
Look at "DemoTVqueryA20022003.mdb (attachment, zip).
Look at Table1 (I added 2 records T1850), look at Query1 (Column Xp1),
look at Module1.
Run Query1 and see.

 

[fkeller]

I think it can be done via Module (function).
Look at "DemoTVqueryA20022003.mdb (attachment, zip).
Look at Table1 (I added 2 records T1850), look at Query1 (Column Xp1),
look at Module1.
Run Query1 and see.

Cannot see module1. only the query! understand the query, but need to see how your getting the previous record odometer reading.