Others

How to Calculate Duration Between Timestamps in Microsoft Access Queries

Elise WilliamsElise Williams Nov 4, 2026 869 views

Question details

Calculate the time duration in hours between consecutive timestamps in a database query, grouped by MRN and medication, with a default value for the final row.

How to Calculate Duration Between Timestamps in Microsoft Access Queries
Product
Microsoft Access
Device & OS
not provided
Scenario
A user needs to find the time difference between consecutive records for specific patient data (MRN) and medications, ensuring the final row in each group defaults to 1 hour.
Observed behavior
The user requires an SQL query logic that groups by MRN and medication, sorts by action time, compares timestamps across rows, and outputs the calculated difference in hours.
Before you start

Before modifying your queries or sharing your database for troubleshooting, ensure your database is backed up and all patient information (such as Medical Record Numbers) is sanitized or replaced with dummy data.

Solution 1Recommended

Use a Subquery to Calculate Time Differences Between Rows

Use an SQL correlated subquery in Access to fetch the next timestamp for the same MRN and medication group to calculate the duration.

Since Microsoft Access does not support standard window functions like LEAD() or LAG() found in SQL Server, you must use a correlated subquery to find the 'next' record. By finding the minimum timestamp that is greater than the current row's timestamp within the same MRN and Medication group, you can calculate the duration.

1
Open Query Design in SQL View

Open your database in Microsoft Access, navigate to the 'Create' tab on the ribbon, click on 'Query Design', close the tables dialog, and right-click the query tab to select 'SQL View'.

2
Write the Correlated Subquery

Create a subquery in your SELECT statement that retrieves the TOP 1 timestamp from the same table where the MRN and Medication match the main query, and the timestamp is strictly greater than the current row's timestamp.

3
Calculate the Difference in Hours

Subtract the current row's timestamp from the subquery's resulting timestamp. Since Access stores dates as days, multiply this difference by 24 to convert the duration into hours.

4
Apply Default Value for the Final Row

Wrap your calculation in an IIf() and IsNull() function combination. If the subquery returns Null (meaning it is the last record in the group), instruct the query to output the default value of 1 instead of the calculated duration.

Use a Subquery to Calculate Time Differences Between Rows
Privacy Warning: Never share unsanitized databases containing real Medical Record Numbers (MRN) or Protected Health Information (PHI) on public forums or file-sharing websites. Always use dummy data when asking for database help.
Free Microsoft Office alternative

Need a Free, Lightweight Office Suite for Data Analysis?

While Microsoft Access is used for relational databases, WPS Office provides an outstanding alternative for Microsoft Word, Excel, and PowerPoint. If your data calculation needs can be handled in spreadsheets, WPS Spreadsheet makes time calculations and grouping incredibly easy without writing complex SQL queries.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Intuitive interface for complex time calculations, grouping, and pivot tablesLightweight architecture that runs smoothly on older hardwareFree to download and use with comprehensive productivity tools
microsoft office alternative - wps office

Frequently Asked Questions

How do I calculate time difference in hours in Microsoft Access?

You can subtract two Date/Time fields in Access, which returns the difference in days. To convert it to hours, multiply the result by 24. Alternatively, you can use the DateDiff('h', [StartTime], [EndTime]) function, though DateDiff rounds to the nearest hour boundary.

How do I get the value of the next row in an Access query?

Because Access SQL does not support window functions like LEAD(), you must use a correlated subquery or the DLookup function to find the minimum (earliest) timestamp that is greater than the current row's timestamp for the same grouping criteria.

How do I handle the last record in an Access group calculation?

Wrap your time calculation in an IIf() and IsNull() function. If the subquery searching for the next record returns Null, output your desired default value (such as 1) instead of the formula.