Query to work out reaction times

[Mudface]

Nice easy one to start with....

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 (from tReactors), 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. 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 (((tSamples.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;

 

[Mudface]

Got it . I had to add a copy of the tSamples table, and use inner left joins on both. This is the SQL view if anyone's interested (or still awake):-

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));