Query to work out times

[Chris Howarth]

Just wondered if this was possible or whether I need to adjust the set-up of my tables. I'm trying to work out the time taken for a chemical reaction to finish. I have a main table (tReactors) linked one-to-many by BatchID to a samples table (tSamples). The main table stores details of the reaction batch, the samples table stores analytical details of samples taken during the reaction. Each sample has an identifier as to the reaction point where it was taken- I want to work out the time difference between the Start Gas Date and Time and the Reaction End Date and Time, but can't seem to get my query syntax right.

tReactors
BatchID (pk)
BatchNo
Date
Time
Material
Other Fields...

tSamples
SampleID (pk)
BatchID (fk)
Date
Time
ReactionPoint (lookup from tReactionPoints)
Analytical Fields


I've tried setting up a query containing BatchID, Date, Time (both from tSamples) and ReactionPoint (restricted to show only the Start Gas and Reaction End points) but can't work out (other than doing query after query) how to get the time difference between them. Thanks for help in advance.

Forgot to add, the following three queries give me what I want, but I'm sure there's a more efficient one-step query which is what I'm after (ReactionPoint=8 is Reaction End, ReactionPoint = 6 is Start Gas):-

Query2

SELECT [tSamples]![Date]+[tSamples]![Time] AS Expr1, tReactors.BatchNo
FROM tReactors INNER JOIN tSamples ON tReactors.BatchID = tSamples.BatchID
WHERE (((t.ReactionPoint)=8));

Query3

SELECT tSamples!Date+tSamples!Time AS Expr2, tReactors.BatchNo
FROM tReactors INNER JOIN tSamples ON tReactors.BatchID=tSamples.BatchID
WHERE (((tSamples.ReactionPoint)=6));

Query4

SELECT (Query2!Expr1-Query3!Expr2)*24 AS BatchLength, Query3.BatchNo
FROM Query2 INNER JOIN (Query3 INNER JOIN tReactors ON Query3.BatchNo = tReactors.BatchNo) ON Query2.BatchNo = tReactors.BatchNo;

 

[raskew]

Date and Time are both reserved words and should
not be used as field names. The following example
renames them as Bdate, Btime in tReactors and
Sdate, Stime in tSamples.

This is setup as a parameter query, allowing the user
to specify the desired ReactionPoint. It employs the
DateDiff() function to determine elapsed time (in this
example set as minutes)

PARAMETERS [Enter ReactionPoint] Short;
SELECT tReactors.BatchNo, DateDiff("n",[BDate]+[BTime],[SDate]+[STime]) AS runtime, tSamples.ReactionPoint
FROM tReactors LEFT JOIN tSamples ON tReactors.BatchID = tSamples.BatchID
WHERE (((tSamples.ReactionPoint)=[Enter ReactionPoint]));

 

[Chris Howarth]

Sorry, I don't think I made myself clear enough in the first post. Both sets of date and time will be in the tSamples table, what I'm trying to do is subtract the date and time identified by the ReactionPoint 'Start Gas' from the ReactionPoint identified 'Batch End' from the same table.

 

[Chris Howarth]

Heh, got it

Only took two days... I had to add a copy of the tSamples table to the query- this is the SQL view:-

SELECT tReactors.BatchNo, tSamples!sDate+tSamples!sTime AS Start, tSamples_1!sDate+tSamples_1!sTime AS [End], ([End]-[Start])*24 AS BatchLength
FROM (tReactors LEFT JOIN tSamples AS tSamples_1 ON tReactors.BatchID = tSamples_1.BatchID) LEFT JOIN tSamples ON tReactors.BatchID = tSamples.BatchID
WHERE ((([tSamples]![ReactionPoint])=6) AND (([tSamples_1]![ReactionPoint])=8));

Don't know if this is the 'correct' way of doing it, but it works.

 

[raskew]

Chris-

Excellent solution! Got hung up last night trying
to provide a similar way to go.

After playing with your code, thought you might
want to try the following to provide a more 'user
friendly' BatchLength.

SELECT tReactors.BatchNo,
tSamples!sDate+tSamples!sTime AS Start,
tSamples_1!sDate+tSamples_1!sTime AS End,
DateDiff("n",[start],[end])\60 & " hours " 
& DateDiff("n",[start],[end]) Mod 60 & " minutes" AS BatchLength
FROM (tReactors LEFT JOIN tSamples AS tSamples_1 ON 
tReactors.BatchID = tSamples_1.BatchID) LEFT JOIN
tSamples ON tReactors.BatchID = tSamples.BatchID
WHERE ((([tSamples]![ReactionPoint])=6) AND
(([tSamples_1]![ReactionPoint])=8));

Bob

 

[Chris Howarth]

Excellent, thanks very much Bob, I was wondering about how to show times in that way. Bit of a shame Access doesn't seem to 'natively' support the [hh]:mm format of Excel- very useful for times in excess of 24 hours.