Query for next record of greater Time Value, subject to other constr. 1

[Gerbers404]

I have a query that combines 2 existing queries. The 1st query contains Start of Test information (Start Time), and the 2nd contains End of Test information (Stop Time) in an attempt to give me the date differential of any test performed on a specific specimen.

I define a test in such a way as that the Specimen Number and GageID (equipment used) must match between the 1st and 2nd query.

The query works well except in the instance where the same specimen is tested with the same GageID more than once (an entirely possible scenario). When this happens, the query returns records for both the original start and stop times for the first test (good), the original start and stop times for the 2nd test (good), and the start time for the 1st test combined with the stop time for the 2nd test (bad). So the resulting query could look like this:

Specimen|GageID| Start | Stop |Elapsed
1 12 2:00 2:15 15 Good
1 12 4:00 4:15 15 Good
1 12 2:00 4:15 135 Bad

I need to find some way to only select a Start Time record and the next closest Stop Time record that has a value greater than the Start Time.

Here is the SQL for my query:

SELECT DISTINCT qryStartActivitiesBySpecNum.Specimen_Num, qryStartActivitiesBySpecNum.TStamp, qryStartActivitiesBySpecNum.Gage_ID, qryStartActivitiesBySpecNum.Activity_Desc, qryStartActivitiesBySpecNum.Formal, qryStartActivitiesBySpecNum.Action, qryStopActivitiesBySpecNum.Specimen_Num, qryStopActivitiesBySpecNum.TStamp, qryStopActivitiesBySpecNum.Gage_ID, qryStopActivitiesBySpecNum.Activity_Desc, qryStopActivitiesBySpecNum.Formal, qryStopActivitiesBySpecNum.Action, DateDiff("n",[qryStartActivitiesBySpecNum.TStamp],[qryStopActivitiesBySpecNum.TStamp]) AS [Elapsed Time (Min)]
FROM qryStartActivitiesBySpecNum INNER JOIN qryStopActivitiesBySpecNum ON (qryStartActivitiesBySpecNum.Specimen_Num = qryStopActivitiesBySpecNum.Specimen_Num) AND (qryStartActivitiesBySpecNum.Gage_ID = qryStopActivitiesBySpecNum.Gage_ID)
WHERE (((qryStartActivitiesBySpecNum.Specimen_Num)=[Forms]![frmSpecimenReport]![txtSpecNum]) AND ((qryStopActivitiesBySpecNum.Specimen_Num)=[Forms]![frmSpecimenReport]![txtSpecNum]) AND (([qryStopActivitiesBySpecNum.TStamp])>[qryStartActivitiesBySpecNum.TStamp]));

Does anyone have an idea how I might be able to get this working?

Thank you for taking the time to read this post. Any help would be greatly appreciated!


Gerbers404

 

[Golom]

Your query is returning all record combinations for which the condition

(([qryStopActivitiesBySpecNum.TStamp])>[qryStartActivitiesBySpecNum.TStamp]

is true. You of course want only the first such record.

Try this

SELECT DISTINCT 
S.Specimen_Num, S.TStamp, S.Gage_ID, S.Activity_Desc, S.Formal, S.Action, E.Specimen_Num, E.TStamp, E.Gage_ID, E.Activity_Desc, E.Formal, E.Action, DateDiff("n",[S.TStamp],[E.TStamp]) AS [Elapsed Time (Min)]

FROM qryStartActivitiesBySpecNum As S INNER JOIN qryStopActivitiesBySpecNum As E 
ON S.Specimen_Num = E.Specimen_Num 
AND S.Gage_ID = E.Gage_ID

WHERE S.Specimen_Num=[Forms]![frmSpecimenReport]![txtSpecNum] 
      AND E.Specimen_Num=[Forms]![frmSpecimenReport]![txtSpecNum] 
      AND [S.TStamp] = 
     (SELECT MIN(X.TStamp) From qryStopActivitiesBySpecNum As X
      Where X.Specimen_Num = S.Specimen_Num 
            AND X.Gage_ID = S.Gage_ID
            AND X.TStamp > S.TStamp)

 

[Golom]

OOps. Sorry ... that should be

SELECT DISTINCT 
S.Specimen_Num, S.TStamp, S.Gage_ID, S.Activity_Desc, S.Formal, S.Action, E.Specimen_Num, E.TStamp, E.Gage_ID, E.Activity_Desc, E.Formal, E.Action, DateDiff("n",[S.TStamp],[E.TStamp]) AS [Elapsed Time (Min)]

FROM qryStartActivitiesBySpecNum As S INNER JOIN qryStopActivitiesBySpecNum As E 
ON S.Specimen_Num = E.Specimen_Num 
AND S.Gage_ID = E.Gage_ID

WHERE S.Specimen_Num=[Forms]![frmSpecimenReport]![txtSpecNum] 
      AND E.Specimen_Num=[Forms]![frmSpecimenReport]![txtSpecNum] 
      AND [[COLOR=red]E[/color].TStamp] = 
     (SELECT MIN(X.TStamp) From qryStopActivitiesBySpecNum As X
      Where X.Specimen_Num = S.Specimen_Num 
            AND X.Gage_ID = S.Gage_ID
            AND X.TStamp > S.TStamp)

 

[Gerbers404]

Thank you Golom, this worked perfectly!

Now that I see what you did, it makes perfect sense to me.

Thanks Again!

Gerbers404