Gerbers404
Programmer
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:
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
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:
Code:
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