largest gap between dates

[KLewisBPM]

Can anyone think of a way i could query a table to look at all the records and determine the longest gap between 2 dates. The table contains many records and by filtering for an employee id and machine I want to know the longest they have ever gone without running the said machine.

Any help on this would be great

KLewisBPM

 

[Remou]

Do you mean:

SELECT X.Computer, Max(X.Diff) AS MaxOfDiff
FROM (SELECT tblTable.Computer, tblTable.LogDate, 
      CDate(Nz((SELECT Top 1 A.LogDate 
          FROM tblTable A 
          WHERE A.Computer=tblTable.Computer 
          AND A.LogDate>tblTable.Logdate),Date())) 
      AS NextLog, [NextLog]-[LogDate] AS Diff
      FROM tblTable
      ORDER BY tblTable.Computer, tblTable.LogDate) 
AS X
GROUP BY X.Computer;