# Thread: Query- Calculate the difference between rows

1. Registered User
Join Date
Aug 2012
Posts
126

## Unanswered: Query- Calculate the difference between rows

Hey guys,

I have a query that shows likes this:

[employeeATask1] - [Start Time] - [EndTime]
[EmployeeATask3] - [Start Time] - [EndTime]

What i'd like to be able to do is take the end time from task 1 and substract it from the start time of task 2. Then substract the end time of task 2 from the start time of task 3. Basically subtracting the previous end time from the start time of the next record.

Is there a way to accomplish this in a query?

2. Moderator
Join Date
Mar 2009
Posts
5,442
How is the data set sorted? Are [employeeATask1], [Start Time],[EndTime], etc the actual names? Are there other columns in the query?

3. Registered User
Join Date
Aug 2012
Posts
126
Originally Posted by Sinndho
How is the data set sorted? Are [employeeATask1], [Start Time],[EndTime], etc the actual names? Are there other columns in the query?
This is my SQL for the query. There are a few other columns in the query and the actual names are different than what i posted.

This is a list of the fields in the query
Int,
UserID,
Date,
EndingWorkArea,
StatusFlag,
StartTime,
EndTime,
Minutes,
Location,
QtyAllocated,
QtyPulled

4. Moderator
Join Date
Mar 2009
Posts
5,442
1. In a standard module create a function, like this:
Code:
```Public Function TimeDiff(ByVal RowID As Variant) As Variant

Dim dteStart As Variant
Dim dteEnd As Variant

dteStart = DLookup("StartTime", "Query2", "TaskNumber=" & RowID)
dteEnd = DLookup("EndTime", "Query2", "TaskNumber>" & RowID)
TimeDiff = DateDiff("n", dteStart, dteEnd)

End Function```
2. In the function, change "Query2" to the actual name of your query. You can also change the first parameter of the DateDiff() function (n) if you want to return the time difference in another unit than minutes.

3. call the function TimeDiff() from your query:
Code:
```SELECT qryTasks.TaskNumber, qryTasks.TaskDescription, qryTasks.Int, qryTasks.UserID, qryTasks.Date, qryTasks.EndingWorkArea, qryTasks.TaskType, qryTasks.StatusFlag, qryTasks.TaskGenerationRefNbr, qryTasks.StartTime, qryTasks.EndTime, DateDiff("n",[qryTasks]![StartTime],[qryTasks]![EndTime]) AS Minutes, [qryTaskDetail]![DestinationAisle] & "-" & [qryTaskDetail]![DestinationBay] & "-" & [qryTaskDetail]![DestinationLevel] AS Location, qryTaskDetail.QtyAllocated, qryTaskDetail.QtyPulled, TimeDiff(qryTasks.TaskNumber) AS TimeDiff