Results 1 to 4 of 4
  1. #1
    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]
    [EmployeeATask2] - [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. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How is the data set sorted? Are [employeeATask1], [Start Time],[EndTime], etc the actual names? Are there other columns in the query?
    Have a nice day!

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by Sinndho View Post
    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.

    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
    FROM qryTaskDetail INNER JOIN qryTasks ON qryTaskDetail.TaskNumber = qryTasks.TaskNumber
    GROUP BY 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]), [qryTaskDetail]![DestinationAisle] & "-" & [qryTaskDetail]![DestinationBay] & "-" & [qryTaskDetail]![DestinationLevel], qryTaskDetail.QtyAllocated, qryTaskDetail.QtyPulled
    ORDER BY qryTasks.TaskNumber, qryTasks.UserID, qryTasks.StartTime;
    This is a list of the fields in the query
    TaskNumber,
    TaskDescription,
    Int,
    UserID,
    Date,
    EndingWorkArea,
    TaskType,
    StatusFlag,
    TaskGenerationRefNbr,
    StartTime,
    EndTime,
    Minutes,
    Location,
    QtyAllocated,
    QtyPulled

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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
    FROM qryTaskDetail INNER JOIN qryTasks ON qryTaskDetail.TaskNumber = qryTasks.TaskNumber
    GROUP BY 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]), [qryTaskDetail]![DestinationAisle] & "-" & [qryTaskDetail]![DestinationBay] & "-" & [qryTaskDetail]![DestinationLevel], qryTaskDetail.QtyAllocated, qryTaskDetail.QtyPulled
    ORDER BY qryTasks.TaskNumber, qryTasks.UserID, qryTasks.StartTime;
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •