How to Subtract the Previous Record from the Current Record in Access
Question details
The user needs to calculate the difference between a numeric field in the current record and the same field in the immediately preceding record in a database table.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Calculating running totals or differences between sequential rows in a database table.
- Observed behavior
- The user wants to generate a 'Total Hours Run' value by subtracting the previous record's hours from the current record's hours, but requires a method to reference the previous row since Access tables lack an inherent order.
Ensure your Access table has a unique, sequential identifier (such as an AutoNumber ID or a precise Date/Time field) to properly define the row order before attempting to calculate differences.
Use a Correlated Subquery to Find the Previous Record
Retrieve the value from the immediately preceding row using a SQL subquery based on a sequential ID.
Because Microsoft Access tables do not store records in a guaranteed sequential order, you must explicitly define the sequence. A correlated subquery allows you to find the maximum ID that is strictly less than the current row's ID, effectively identifying the 'previous' record.
Open your table in Design View and ensure it has a primary key or a sequential identifier, such as an AutoNumber field named 'RecordID'.
Navigate to the 'Create' tab on the Access ribbon and click 'Query Design'. Close the 'Show Table' dialog and switch to 'SQL View'.
Enter a SQL statement that selects the current record's fields and uses a subquery to fetch the previous record's value. For example: SELECT T1.RecordID, T1.NumberOfHours, (SELECT TOP 1 T2.NumberOfHours FROM YourTable AS T2 WHERE T2.RecordID < T1.RecordID ORDER BY T2.RecordID DESC) AS PreviousHours FROM YourTable AS T1;
Modify the query to perform the subtraction directly: SELECT T1.RecordID, T1.NumberOfHours, (T1.NumberOfHours - Nz((SELECT TOP 1 T2.NumberOfHours FROM YourTable AS T2 WHERE T2.RecordID < T1.RecordID ORDER BY T2.RecordID DESC), 0)) AS TotalHoursRun FROM YourTable AS T1;

Use a Self-Join in Query Design
Join the table to itself to align the current record with the previous record in the same row.
Easily Manage and Calculate Data with WPS Office
While Microsoft Access is designed for complex relational databases, WPS Office provides a free, lightweight, and highly compatible alternative for everyday data analysis. With WPS Spreadsheet, you can easily calculate record differences, manage datasets with a familiar user interface, and seamlessly migrate your existing work.
- 1. Download and Install WPS Office: Visit the official WPS website to download the free office suite and install it on your device.
- 2. Open Your Data in WPS Spreadsheet: Launch WPS Spreadsheet and open your existing data file or export your Access table to an Excel/CSV format and open it.
- 3. Calculate Row Differences Instantly: Simply click the cell next to your current record and type a formula like '=B3-B2' to instantly subtract the previous record's value, then drag the fill handle down to apply it to all rows.

Frequently Asked Questions
Why can't I just subtract the row above it like in Excel spreadsheets?
Unlike spreadsheet applications, relational databases like Microsoft Access do not store records in a guaranteed sequential order. Because 'row above' has no meaning in a database engine without explicit sorting, you must use an identifier like an ID or Date/Time field to define what the 'previous' record is.
What happens to the calculation on the very first record?
Since there is no preceding record for the first entry, a standard subtraction query will result in a null (blank) value. You can wrap the subquery in the Nz() function, which converts the null value into a zero or another specified number, allowing the calculation to process correctly.
Can I calculate the difference between records using VBA?
Yes. You can write a VBA script using DAO or ADO Recordsets to loop through the records sequentially, store the previous value in a variable, and calculate the difference. However, using a SQL correlated subquery is generally recommended as it is processed directly by the database engine and can be easily used in reports and forms.




