How to Calculate Duration Between Timestamps in Microsoft Access Queries
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.

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

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.

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.




