# 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.

Sunday, June 10, 2018 9:29 AM

### All replies

• 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

Regards,VKKT

Saturday, June 9, 2018 9:02 AM
• 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)

Sunday, June 10, 2018 9:47 AM
• 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

Monday, June 11, 2018 8:04 AM
• 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)

Monday, June 11, 2018 10:10 AM
• 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
Monday, June 11, 2018 11:21 AM
• Could you post the complete SQL that you have?

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

Monday, June 11, 2018 2:17 PM
• 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:

Regards, VKKT
Tuesday, June 12, 2018 5:29 AM
• 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)

Tuesday, June 12, 2018 7:54 AM
• 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

Tuesday, June 12, 2018 9:08 AM
• 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)

Tuesday, June 12, 2018 9:47 AM
• 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

Tuesday, June 12, 2018 10:32 AM
• 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)

Tuesday, June 12, 2018 4:15 PM
• 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.

Regards, VKKT

Wednesday, June 13, 2018 8:48 AM
• 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)

• Marked as answer by Thursday, June 14, 2018 9:39 AM
Wednesday, June 13, 2018 11:11 AM
• Thanks a lot Mr. Hans for your support in solving the issue.. regards.VKKT
Thursday, June 14, 2018 9:39 AM