How to calculate difference of previous and current record a

[VKKT]

Hi Mr. Hans,

Refer to the subject and as advised attached an the table and report generated. Now I can generate report but calculations/difference for certain records are not showing in the report as highlighted in blue.

Thanks for your support
Regards, VKKT

[HansV]

Welcome to Eileen's Lounge!

Thanks for the sample file; I imported the table into a database and I see the problem. I will investigate it and get back to you.

[HansV]

There are multiple records with the same date and time, for example

S2233.png

Can we ignore records with AncilliaryServices values such as Lubricant, Oil Filter and Car Wash? Or rather: which AncilliaryServices values should we include?

By the way: The records with ID = 1980 and ID = 2488 contain an invalid time value in TransactionTime. I recommend correcting or clearing TransactionTime in these records.

[HansV]

I see now that we only need to look at Super and Special.

[VKKT]

Hi Mr. Hans

Yes we can ignore those items in the query, but need to keep in the table.

I tried with a table without those items, but still the same result.

I will correct the error.

Thanks a lot...VKKT

[HansV]

I'm still investigating the problem. Stay tuned.

[HansV]

One other thing: differences were missing because the transaction time is empty in many records. In the attached sample database, I have set TransactionTime to 12:00 AM in all records where is was empty.

I added a query qrySpecialSuper to select only those records that have an odometer reading and that have Special or Super as ancilliary services:

SELECT VehicleData.*
FROM VehicleData
WHERE (((VehicleData.OdometerReading) Is Not Null) AND ((VehicleData.AncilliaryServices) In ('Special','Super')));

The following query will compute the differences:

SELECT qrySpecialSuper.VehicleNo, qrySpecialSuper.TransactionDate, qrySpecialSuper.Transactiontime, qrySpecialSuper.AncilliaryServices, qrySpecialSuper.OdometerReading, qrySpecialSuper.OdometerReading-(SELECT T.OdometerReading FROM qrySpecialSuper AS T WHERE T.VehicleNo = qrySpecialSuper.VehicleNo AND T.TransactionDate+T.TransactionTime = (SELECT Max(S.TransactionDate+S.TransactionTime) FROM qrySpecialSuper AS S WHERE S.VehicleNo = qrySpecialSuper.VehicleNo AND S.TransactionDate+S.TransactionTime < qrySpecialSuper.TransactionDate+qrySpecialSuper.TransactionTime)) AS Difference
FROM qrySpecialSuper
ORDER BY qrySpecialSuper.VehicleNo, qrySpecialSuper.TransactionDate, qrySpecialSuper.Transactiontime;

You'll see some weird results; this is because the OdometerReading field contains errors.

The query is slow because it has to perform a very large number of calculations.

Odometer.zip

[HansV]

Here is an example of the output:

S2235.png

[VKKT]

Thanks you very much Mr. Hans,

Thanks for your dedicated effort to solve my issue as my boss was behind me for this since last week. You are great.
It is working now very good.
Again than you for your support. I will come back to you if I have any such issues in future.
Regards,
VKKT

[VKKT]

Hi Mr. Hans,

Hope you are doing fine, sorry to bother you again.

Now when I added two more fields in the table the query is not working with the message that "At Most one record can be returned by this subquery"

Thank you in advance for your help
Regards, VKKT

[HansV]

The table contains couples (and one triple) of records with the SAME VehicleNo, TransactionDate and TransactionTime, but DIFFERENT OdometerReading values. For example, look at ID = 367 and 368, or at ID = 863, 864 and 865. There must be something wrong there...

If I remove the duplicate records, the query works without error.

[VKKT]

Hi Mr. Hans,

Thanks for your response with solution.

As I have cleared all the duplicate entries, still the same message appears, but I am doing a thorough checking of it again and will come back to you.

Thanks a lot for your help

regards, VKKT

[HansV]

Here is a query that you can use to identify the duplicates:

SELECT qrySpecialSuper.[VehicleNo], qrySpecialSuper.[TransactionDate], qrySpecialSuper.[Transactiontime], qrySpecialSuper.[ID], qrySpecialSuper.[TranTime], qrySpecialSuper.[FuelVolume], qrySpecialSuper.[OdometerReading], qrySpecialSuper.[OdoReading], qrySpecialSuper.[FuelCost], qrySpecialSuper.[Mileage], qrySpecialSuper.[Product], qrySpecialSuper.[AncilliaryServices], qrySpecialSuper.[Location], qrySpecialSuper.[CarUser], qrySpecialSuper.[ReceiptReceived], qrySpecialSuper.[Remarks], qrySpecialSuper.[Reading#]
FROM qrySpecialSuper
WHERE (((qrySpecialSuper.[VehicleNo]) In (SELECT [VehicleNo] FROM [qrySpecialSuper] As Tmp GROUP BY [VehicleNo],[TransactionDate],[Transactiontime] HAVING Count(*)>1 And [TransactionDate] = [qrySpecialSuper].[TransactionDate] And [Transactiontime] = [qrySpecialSuper].[Transactiontime])))
ORDER BY qrySpecialSuper.[VehicleNo], qrySpecialSuper.[TransactionDate], qrySpecialSuper.[Transactiontime];

[VKKT]

Hi Mr. Hans,

Thanks for your response.

Actually these are not duplicate entries, in some days fuel filling done twice but should be with different odometer reading, so I cant remove that duplicate entry. (I was manually adding all the multiple entries) So is there is any other way to solve this issue.

Is it possible to add up the multiple entries in one day, for fuel volume, fuel cost, odometer reading(should take the higher value, cannot add). Can exclude the transaction time while doing any queries.

Regards, VKKT

[HansV]

But how can the odometer reading be 297042, 297366 and 297666 at one moment in time, 11-Jan-18 02:00 PM?

[HansV]

Does this do what you want?

SELECT qrySpecialSuper.VehicleNo, qrySpecialSuper.TransactionDate, qrySpecialSuper.AncilliaryServices, qrySpecialSuper.OdometerReading, qrySpecialSuper.OdometerReading-(SELECT Max(T.OdometerReading) FROM qrySpecialSuper AS T WHERE T.VehicleNo = qrySpecialSuper.VehicleNo AND T.TransactionDate = (SELECT Max(S.TransactionDate) FROM qrySpecialSuper AS S WHERE S.VehicleNo = qrySpecialSuper.VehicleNo AND S.TransactionDate < qrySpecialSuper.TransactionDate) GROUP BY T.VehicleNo, T.TransactionDate) AS Difference
FROM qrySpecialSuper
ORDER BY qrySpecialSuper.VehicleNo, qrySpecialSuper.TransactionDate;

[VKKT]

Thanks Mr. Hans for your response,

Actually the time which were missing in the table which I had entered a rough time (like 2:00 PM) in order to have a time entry to run the query. Now it is not required as you excluded the time field and the new query is running now.
The odometer reading is a calculated average but it is also wrong. Because the multi entries you pointed out is generated due to the oil company card shows errors and the driver 3 times swipe the card to fill the fuel, so this minor figure appears in the invoice. hence the average calculation came wrong.

Any way the new query is working now without any issue.

Thank you very much for your dedicated effort for the same.

Have a great day, regards, VKKT

[VKKT]

Hi Mr. Hans,
Good Morning.
Hope you are doing fine.
I am back again.
Is it possible to generate separate graph for each vehicle based on Transaction Date and Odometer Reading to know the trend of the kilometer run by each vehicle.
Thanks you in advance for your support.
, VKKT

[HansV]

Your sample table contains more than 60 different vehicles. A chart with that many series would be very difficult to interpret...

[VKKT]

Hi Mr. Hans,

Yes, you are right. Thanks for your valuable time & response, It is OK.

VKKT