Finding the difference in two odometer readings

[Amanda003]

I have a database that I have created for my job to track daily fuel and mileage for 60+ pieces of equipment. On my main table I have RecordID, Quarter, Date, Branch, Truck #, Begining Odometer, Ending Odometer and a calculated field for Daily Total Miles. I then linked that database to a new one and have a query for each truck so I can easily see the list of readings without using parameters each time. I have to account for every mile so is there a expression I can use in the linked odometer reading database that will take the beginning odometer on the most current date and show me if there is a difference between the previous date ending odometer?

Example:

Q2 4/27/20 Dallas #21 89,000 89,400 400
Q2 4/30/20 Dallas #21 90,000 90,500 500

I need an expression or way to show in the query that there are 600 miles missing between 4/27 & 4/30.

I hope I explained that well enough. Thank you in advance!

 

[theDBguy]

Hi. You could try using DLookup().
Sent from phone...

 

[Amanda003]

Hi. You could try using DLookup().
Sent from phone...

I have seen that when I have tried to search for my answer but unsure how to use DLookup(). Could you possibly send me an example of what it might look like using the field names I have provided?

 

[MajP]

This can be done in a query using a subquery. For each reading you subtract the previous reading for the given vehicle. Can you post real example data? Just need the one table.

 

[MajP]

Really not of any use. Could care less of those query, would like to see a table with data. Wow that was a lot of work. You know you could have wrote one parameterized query, where you supply vechicle number.

 

[Amanda003]

The table with the data is in that database under IFTA Sheet. Yes, it was a lot of work and my Access knowledge is fairly basic. It seemed easier for me to do a query for each truck once rather than having to supply a bunch of parameters each time.

 

[The_Doc_Man]

That is a list of dozens of queries that can't be opened in anything other than design mode because the data is coming from linked tables. No way we can easily work with that.

There are all sorts of articles here on "find previous record" which would show you how to correlate records using an ordered query so that you could see data from the previous and current record. I have to rush off shortly (grocery day) so I need to just point out that it should be possible to find records where current "BeginOdometer" doesn't match the previous "EndOdometer" by using a sub-query.

It seemed easier for me to do a query for each truck once rather than having to supply a bunch of parameters each time.

Seems that way - but probably isn't. So you have 60 queries. And if you add trucks, you'll need more. Speed wise, the query with a couple of extra parameters will run just a quickly as the ones that are fixed by a single, hard-coded truck ID.

 

[theDBguy]

The table with the data is in that database under IFTA Sheet. Yes, it was a lot of work and my Access knowledge is fairly basic. It seemed easier for me to do a query for each truck once rather than having to supply a bunch of parameters each time.

Hi. I agree with @MajP. You gave us a linked database instead of where the data is, which would be the file called "2020 IFTA Mileage and Fuel.accdb"

 

[Amanda003]

Here is the Main Database

 

[Amanda003]

Hi. I agree with @MajP. You gave us a linked database instead of where the data is, which would be the file called "2020 IFTA Mileage and Fuel.accdb"

I apologize, I did not realize the data would not be able to be seen.

 

[MajP]

Here is the demo
tblOdometer

ID	VehicleID	StartReading	EndReading	DateRead
1​	21	0​	10​	4/28/2020​
2​	21	10​	20​	4/29/2020​
3​	21	25​	30​	4/30/2020​
4​	21	35​	40​	5/2/2020​
5​	22	20​	40​	4/1/2020​
6​	22	40​	50​	4/3/2020​
7​	22	55​	70​	4/24/2020​
8​	23	100​	120​	4/1/2020​
9​	23	120​	130​	3/29/2020​
10​	24	200​	210​	4/28/2020​

SELECT
A.VehicleID,
A.StartReading,
A.EndReading,
A.DateRead,
(Select Top 1 B.EndReading from tblOdometer as B WHERE A.VehicleID = B.VehicleID AND A.DateRead > B.DateRead Order By B.DateRead Desc) as PreviousReading
FROM tblOdometer AS A
Order By DateRead
;

Query1

VehicleID	StartReading	EndReading	DateRead	PreviousReading
23	120​	130​	3/29/2020​	​
23	100​	120​	4/1/2020​	130​
22	20​	40​	4/1/2020​	​
22	40​	50​	4/3/2020​	40​
22	55​	70​	4/24/2020​	50​
24	200​	210​	4/28/2020​	​
21	0​	10​	4/28/2020​	​
21	10​	20​	4/29/2020​	10​
21	25​	30​	4/30/2020​	20​
21	35​	40​	5/2/2020​	30​

Now just make another query subtracting previous reading from StartReading where value <> 0

SELECT Query1.VehicleID, Query1.StartReading, Query1.EndReading, Query1.DateRead, Query1.PreviousReading, [PreviousReading]-[StartReading]<>0 AS MissingData
FROM Query1
WHERE ((([PreviousReading]-[StartReading]<>0)<>0));

Query2

VehicleID	StartReading	EndReading	DateRead	PreviousReading	MissingData
23	100​	120​	4/1/2020​	130​	30​
22	55​	70​	4/24/2020​	50​	-5​
21	25​	30​	4/30/2020​	20​	-5​
21	35​	40​	5/2/2020​	30​	-5​

Notice in first case the start info is less than the previous end info identifying bad data entry, versus missing data entry.

 

[theDBguy]

Here is the Main Database

And just for completeness, this one uses DLookup(). Hope it helps...

 

[Amanda003]

And just for completeness, this one uses DLookup(). Hope it helps...

Thank you! The only issue I am seeing is that sometimes there is more than one driver for a truck on the same date so they turn in 2 sheets that is why it is pulling up a difference. Not sure how I would fix that or if it would even be possible.

 

[theDBguy]

Thank you! The only issue I am seeing is that sometimes there is more than one driver for a truck on the same date so they turn in 2 sheets that is why it is pulling up a difference. Not sure how I would fix that or if it would even be possible.

Maybe you could add a time component to the date when drivers turn in their sheets. So, same dates can be differentiated by time of day. Just a thought...

 

[MajP]

Here is the sql for your db

SELECT A.istruckid, A.entereddate, A.tripdate, A.[beginning odometer], A.[ending odometer], A.[total miles], A.[ending odometer]-[A].[beginning odometer] AS CalcDifference, (SELECT TOP 1 B.[ending odometer]
FROM [ifta sheet] AS B
where A.istruckID = B.istruckID AND A.TripDate > B.TripDate
ORDER BY B.tripdate DESC, B.[Ending Odometer]) AS PreviousReading, [PreviousReading]<>[Beginning Odometer] AS IsMissing
FROM [ifta sheet] AS A
ORDER BY A.istruckid, A.tripdate;

SELECT qryODO.istruckid, qryODO.entereddate, qryODO.tripdate, qryODO.[beginning odometer], qryODO.[ending odometer], qryODO.CalcDifference, qryODO.PreviousReading
FROM qryODO
WHERE (((qryODO.PreviousReading)<>[beginning odometer]))
ORDER BY qryODO.istruckid, qryODO.tripdate;

Do yourself a big favor and get rid of those stupid field lookups in tables. Yours is so confusing. You have a field called isTruckID, captioned to read as Truck, with a lookup field for TruckID. No one looking at your db is going to figure it out. That is a puzzle There is NO place for lookups in a table even if MS gives you the option to hang yourself. Also get rid of names with spaces in them. Only causing more pain.

Read and heed.

The Access Web - The Evils of Lookup Fields in Tables

 

[Amanda003]

Maybe you could add a time component to the date when drivers turn in their sheets. So, same dates can be differentiated by time of day. Just a thought...

I will have to give it some thought. I enter the sheets as they come in so Driver B might get theirs turned in before Driver A. I love what Access can do but at the same time I wish it could be as simple as Excel sometimes. Thank you again for all your help!

 

[Amanda003]

Here is the sql for your db


Do yourself a big favor and get rid of those stupid field lookups in tables. Yours is so confusing. You have a field called isTruckID, captioned to read as Truck, with a lookup field for TruckID. No one looking at your db is going to figure it out. That is a puzzle There is NO place for lookups in a table even if MS gives you the option to hang yourself. Also get rid of names with spaces in them. Only causing more pain.

Thanks for your input.

 

[theDBguy]

I will have to give it some thought. I enter the sheets as they come in so Driver B might get theirs turned in before Driver A. I love what Access can do but at the same time I wish it could be as simple as Excel sometimes. Thank you again for all your help!

Okay. Good luck! Let us know if you need more help.

 

[MajP]

The only issue I am seeing is that sometimes there is more than one driver for a truck on the same date so they turn in 2 sheets that is why it is pulling up a difference. Not sure how I would fix that or if it would even be possible

I did not test this, but the subquery should be able to be modified to handle differences within the same date.

 

[MajP]

I added three records for the same date. See last three

tblOdometer

ID	VehicleID	StartReading	EndReading	DateRead
1​	21	0​	10​	4/28/2020​
2​	21	10​	20​	4/29/2020​
3​	21	25​	30​	4/30/2020​
4​	21	35​	40​	5/2/2020​
5​	22	20​	40​	4/1/2020​
6​	22	40​	50​	4/3/2020​
7​	22	55​	70​	4/24/2020​
8​	23	100​	120​	4/1/2020​
9​	23	120​	130​	3/29/2020​
10​	24	200​	210​	4/28/2020​
11​	24	180​	200​	4/28/2020​
12​	24	170​	175​	4/28/2020​

modified subquery

SELECT A.vehicleid,
       A.dateread,
       A.startreading,
       A.endreading,
       (SELECT TOP 1 B.endreading
        FROM   tblodometer AS B
        WHERE  A.vehicleid = B.vehicleid
               AND A.dateread >= B.dateread
               AND A.endreading > B.endreading
        ORDER  BY B.dateread DESC,
                  B.endreading DESC) AS PreviousReading
FROM   tblodometer AS A
ORDER  BY A.vehicleid,
          A.dateread,
          A.endreading;

Query1

VehicleID	DateRead	StartReading	EndReading	PreviousReading
21	4/28/2020​	0​	10​	​
21	4/29/2020​	10​	20​	10​
21	4/30/2020​	25​	30​	20​
21	5/2/2020​	35​	40​	30​
22	4/1/2020​	20​	40​	​
22	4/3/2020​	40​	50​	40​
22	4/24/2020​	55​	70​	50​
23	3/29/2020​	120​	130​	​
23	4/1/2020​	100​	120​	​
24	4/28/2020​	170​	175​	​
24	4/28/2020​	180​	200​	175​
24	4/28/2020​	200​	210​	200​