Query Q

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

I have a table called orders with 4 fields, Name, OrderNumber, StartTime,
StopTime. I have 50 records in this table which represent 50 orders
processed by a person in one day. I know how to do the time difference from
the start time and the stop for an order, but how do I tell the time
difference of the stop time for order 1 and the start time of order 2? Stop
time of order 2 and the start time of order 3 and so on?

Thanks,
Sean
 
SELECT a.orderID, LAST(a.stopTime), MIN(b.startTime)
FROM myTable AS a LEFT JOIN myTable AS b
ON a.stopTime < b.startTime
GROUP BY a.orderID



should do. Replace myTable with your real table name, two places, and also
the field names: orderID, stopTime and startTime, if appropriate. The
statement has to be typed in the SQL view of a new query.


Hoping it may help,
Vanderghast, Access MVP
 
I used a minimum amount of data in testing this.
SELECT Sean.Name, Sean.OrderNumber, Sean.StopTime, Min(Sean_1.StartTime) AS
[Next Start], Min(DateDiff("n",[Sean].[StartTime],[Sean_1].[StopTime])) AS
[Time Between]
FROM Sean INNER JOIN Sean AS Sean_1 ON Sean.Name = Sean_1.Name
WHERE (((Sean_1.StartTime)>[Sean].[StopTime]))
GROUP BY Sean.Name, Sean.OrderNumber, Sean.StopTime
ORDER BY Sean.StopTime;
 
Back
Top