How to calculate difference of previous and current record and show the value in a separate field

Question

Hi,

I HAVE A TABLE WITH THE BELOW FIELDS, PLEAE HELP ME TO SOLVE THE ISSUE.

Vehicle_No	/Transaction_Date/	Fuel_Volume	/Odometer_Reading/	Fuel_Cost	/Difference
B-18370	A	B	C	D	E
record 1	03-Jan-18	62.26	104448	132
record 2	10-Jan-18	64.88	104808	138	=C2-C1
record 3	17-Jan-18	66.04	105445	140	=C3-C2
record 4	22-Jan-18	58.02	105948	123	=C4-C3
record 5	28-Jan-18	66.04	106279	140	=C5-C4

I need the difference of previous and current odometer reading and show the value in a separate field (E), as shown above.

Thanks in advance. regards, VKKT

Answer 1

HI,

I have a table with the below fields:

Vehicle_No	Transaction_Date/	Fuel_Volume/	Odometer_Reading/	Fuel_Cost	/   ??
B-18370	A	B	C	D	E
1	03-Jan-18	62.26	104448	132	=C2-C1
2	10-Jan-18	64.88		137.55	=C3-C2
3	17-Jan-18	66.04	105445	140	=C4-C3
4	22-Jan-18	58.02		123	=C5-C4
5	28-Jan-18	66.04	106279	140	=C6-C5

I want to calculate the difference between the previous record and the current record (E), as shown above

Please help me to solve the issue.

Regards,VKKT

Answer 2

Create a query like this: SELECT [TableName].*, [TableName].[Odometer_Reading]-(SELECT T.[Odometer_Reading] FROM [TableName] AS T WHERE T.[Vehicle_No] = [TableName].[Vehicle_No] AND T.[Transaction_Date] = (SELECT Max(S.[Transaction_Date]) FROM [TableName] AS S WHERE S.[Vehicle_No] = [TableName].[Vehicle_No] AND S.[Transaction_Date] < [TableName].[Transaction_Date])) AS Difference

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 3

Thanks Mr. Hans for the response,

As I am not an expert in Access in creating such type of queries, if you can explain some more simple way it will be very much helpful to me.

Thanks.VKKT

Answer 4

I'd do the following:

Copy the SQL text (starting with SELECT) and paste it into Notepad.

Press Ctrl+H to activate the Replace dialog

Enter TableName in the Find what box, and enter the actual name of your table in the Replace with box.

Click Replace All.

If necessary, also change the field names to the actual names in your table.

Copy the SQL text.

In Access, click Query Design on the Create tab of the ribbon.

Close the Show Table dialog without adding a table.

Select SQL View from the View dropdown.

Select the text that is there, then paste the SQL text over it.

You can now switch to Design View or Datasheet View.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 5

Thanks Mr. Hans, for the immediate response, you are so generous. I did as you have advised but giving the message that "does not recognize "vehicledata.*" as a valid field name or expression. When I changed it to "[Vehicledata].[*]" it is giving the message that "query input must contain at least one table or query". Regards, VKKT

Answer 6

Could you post the complete SQL that you have?

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 7

Dear Mr. Hans,

Thanks for taking your valuable time to respond to me. Below is the complete SQL:

SELECT [Vehicledata].*, [Vehicledata].[OdometerReading]-(SELECT T.[OdometerReading] FROM [Vehicledata] AS T WHERE T.[VehicleNo] = [Vehicledata].[VehicleNo] AND T.[TransactionDate] = (SELECT Max(S.[TransactionDate]) FROM [Vehicledata] AS S WHERE S.[VehicleNo] = [Vehicledata].[VehicleNo] AND S.[TransactionDate] < [Vehicledata].[TransactionDate])) AS Difference;

Below are the complete list of field in the table:

ID/VehicleNo/TransactionDate/Transactiontime/FuelVolume/FuelCost/OdometerReading/Product

AncilliaryServices/Location/ReceiptReceived/Remarks/CarUser

Regards, VKKT

Answer 8

I'm so sorry, I forgot to add the FROM clause at the end:

SELECT [VehicleData].*, [VehicleData].[OdometerReading]-(SELECT T.[OdometerReading] FROM [VehicleData] AS T WHERE T.[VehicleNo] = [VehicleData].[VehicleNo] AND T.[TransactionDate] = (SELECT Max(S.[TransactionDate]) FROM [VehicleData] AS S WHERE S.[VehicleNo] = [VehicleData].[VehicleNo] AND S.[TransactionDate] < [VehicleData].[TransactionDate])) AS Difference FROM VehicleData;

If you want to take the transaction date AND transaction time into account, change the SQL to

SELECT [VehicleData].*, [VehicleData].[OdometerReading]-(SELECT T.[OdometerReading] FROM [VehicleData] AS T WHERE T.[VehicleNo] = [VehicleData].[VehicleNo] AND T.[TransactionDate]+T.[TransactionTime] = (SELECT Max(S.[TransactionDate]+S.[TransactionTime]) FROM [VehicleData] AS S WHERE S.[VehicleNo] = [VehicleData].[VehicleNo] AND S.[TransactionDate]+S.[TransactionTime] < [VehicleData].[TransactionDate]+[VehicleData].[TransactionTime])) AS Difference FROM VehicleData;

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 9

Thanks Mr. Hans,

2nd one giving the result with the pop up Message that "At most one record can be returned by this subquery" and (can see in the screen before clicking OK)when clik OK everything disappears only #Name? appears with few records like 55 records. Actually there are 2800 records in the table.
And also i want to mention that in some records there is no odometerreading available in the table.

1st one also giving the same result but when I click ok it disappears and only one record with #name" appears.

Regards..VKKT

Answer 10

Does this work better?

SELECT [VehicleData].*, [VehicleData].[OdometerReading]-(SELECT T.[OdometerReading] FROM [VehicleData] AS T WHERE T.[VehicleNo] = [VehicleData].[VehicleNo] AND T.[TransactionDate]+T.[TransactionTime] = (SELECT Max(S.[TransactionDate]+S.[TransactionTime]) FROM [VehicleData] AS S WHERE S.[VehicleNo] = [VehicleData].[VehicleNo] AND S.[TransactionDate]+S.[TransactionTime] < [VehicleData].[TransactionDate]+[VehicleData].[TransactionTime] AND S.[OdometerReading] Is Not Null)) AS Difference FROM VehicleData;

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 11

Hi Mr. Hans,

Yes this is working better but the popup message "At most one record can be returned by this subquery" and when clicks OK everything disappears and shows all fields with #Name? hence I can not make use of the result.

Many many thanks for your effort in solving this issue.

Regards,
VKKT

Answer 12

Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip this copy and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Post a link to the uploaded and shared file in a reply here.

Or register at www.eileenslounge.com (it's free) and start a thread in the Access forum. You can attach files up to 250 KB to a post there.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 13

Hi Mr. Hans,

As advised I have created a thread in the eileenslounge.com with the same subject. and attached the table and the query generated for your reference.

Thanks for your continuous support.

Regards, VKKT

Answer 14

I have posted a reply (with a sample database) in http://www.eileenslounge.com/viewtopic.php?f=29&t=30145

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 15

Thanks a lot Mr. Hans for your support in solving the issue.. regards.VKKT