Subquery Help! Comparing Dates between rows

[JayRab]

Subqueries... never had to use them yet.

I am trying to calculate MTTF (mean time to failure). I have a table of equipment events with date/time fields called TimeDelivered. I need to calculate the datediff() between all TimeDelivered fields for each specific piece of equipment.

So i know I need to use a subquery but not sure how to below is the code for the main query.
[CODE]SELECT [311_30DAYAVAILABILITYWOKRINGTABLE].Equipment, [311_30DAYAVAILABILITYWOKRINGTABLE].TimeDelivered
FROM 311_30DAYAVAILABILITYWOKRINGTABLE
WHERE ((Not ([311_30DAYAVAILABILITYWOKRINGTABLE].TimeDelivered) Is Null))
ORDER BY [311_30DAYAVAILABILITYWOKRINGTABLE].Equipment, [311_30DAYAVAILABILITYWOKRINGTABLE].TimeDelivered;[CODE]




Now on my subquery I need to create another column that contains the next time delivered for 305(or what ever equipment) so for the above picture there will be another column called NextTimedelivered and on first row it will contain [ 1/28/2014 2:38:16 ] I would then added a calculated field to get that date difference.

Here is my main query with subquery code added

SELECT [311_30DAYAVAILABILITYWOKRINGTABLE].Equipment, [311_30DAYAVAILABILITYWOKRINGTABLE].TimeDelivered
(SELECT TOP 1 Dupe.TimeDelivered 
FROM [311_30DAYAVAILABILITYWOKRINGTABLE] AS Dupe
WHERE Dupe.Equipment = [311_30DAYAVAILABILITYWOKRINGTABLE].Equipment
AND Dupe.TimeDelivered > [311_30DAYAVAILABILITYWOKRINGTABLE].TimeDelivered
ORDER BY Dupe.TimeDelivered ASC, Dupe.Equipment) as NextTimeDelivered
FROM 311_30DAYAVAILABILITYWOKRINGTABLE
WHERE ((Not ([311_30DAYAVAILABILITYWOKRINGTABLE].TimeDelivered) Is Null))
ORDER BY [311_30DAYAVAILABILITYWOKRINGTABLE].Equipment, [311_30DAYAVAILABILITYWOKRINGTABLE].TimeDelivered;

I get the error - (At Most one record can be returned by this subquery).

Again never used subquery before so lost on this anyway.

Anyhelp would be appreciated.

ps: I know I misspelled some things in my table names

[ssanfu]

I did this when I made a mileage dB for my vehicles. but I forget how I did it...

Pop over the Roger's Access Library and look at
http://www.rogersaccesslibrary.com/forum/topic445.html

[JayRab]

Thx for the link Ill take a look at it. I figured out what I was doing wrong on it and got it working correctly. As usual i was making it more complicated then it needed to be.