Others

How to Subtract the Previous Record from the Current Record in Access

Amos GikundaAmos Gikunda Oct 23, 2026 870 views

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.

How to Subtract the Previous Record from the Current Record in Microsoft Access
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.
Before you start

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.

Solution 1Recommended

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.

1
Define the Sequential Order

Open your table in Design View and ensure it has a primary key or a sequential identifier, such as an AutoNumber field named 'RecordID'.

2
Create a New Query

Navigate to the 'Create' tab on the Access ribbon and click 'Query Design'. Close the 'Show Table' dialog and switch to 'SQL View'.

3
Write the Correlated Subquery

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;

4
Calculate the Difference

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 Correlated Subquery to Find the Previous Record
Handling the First Record: The first record has no preceding row, so the subquery returns null. Using the Nz() function ensures the null is treated as zero, preventing calculation errors.
Free Microsoft Office alternative

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. 1. Download and Install WPS Office: Visit the official WPS website to download the free office suite and install it on your device.
  2. 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. 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.
Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Calculate row differences instantly without writing complex SQL queries.Lightweight installation and familiar user interface.Free to use for everyday spreadsheet and data management tasks.
microsoft office alternative - wps office

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.