Cycle time query

[Newbie_nick]

I have a database that stores machine cycle information for multiple machines
in my plant, and I would like to generate a query to calculate individual
cycle times for each cycle for each machine. A sample data table is below:

EMPLOYEE DATE_RECORDED MACHINE_ID PROCESS_NAME
1 1/14/09 6:55 A PROCESS_CYCLE_COMPLETE
4 1/14/09 6:55 B PROCESS_CYCLE_COMPLETE
8 1/14/09 6:55 C PROCESS_CYCLE_COMPLETE
9 1/14/09 7:28 D PROCESS_CYCLE_COMPLETE
1 1/14/09 7:29 A PROCESS_CYCLE_COMPLETE
4 1/14/09 7:29 B PROCESS_CYCLE_COMPLETE
8 1/14/09 7:30 C PROCESS_CYCLE_COMPLETE
9 1/14/09 7:31 D PROCESS_CYCLE_COMPLETE
1 1/14/09 7:35 A PROCESS_CYCLE_COMPLETE
4 1/14/09 7:37 B PROCESS_CYCLE_COMPLETE
8 1/14/09 7:38 C PROCESS_CYCLE_COMPLETE
9 1/14/09 7:39 D PROCESS_CYCLE_COMPLETE

Thanks for the help!
Nick

 

[Newbie_nick]

That worked, thank you!

Can you explain through your logic? I can't figure out what it's actually
doing.

Thanks again!
Nick

 

[jutlaux]

I have setup a similar query using your thought process with the SQL shown
below. The problem I have is that when I run the Query the DowntimeCalc field
is empty for all records. What am I missing?

Thanks

SELECT CURE_START_DATE_TIME, PRESS_NAME, DateDiff("s",(SELECT
MAX(CURE_START_DATE_TIME) FROM tblTemporary2 WHERE PRESS_NAME=M.PRESS_NAME
AND CURE_START_DATE_TIME<M.CURE_START_DATE_TIME),[CURE_START_DATE_TIME]) AS
DowntimeCalc
FROM tblTemporary2 AS M;