G
Guest
Query to determine change over time from one mold to another. Measured as
last good of mold “A†to first good part of Mold “Bâ€.
For the most part it works, except for second shift. Second shift runs past
midnight. The query works except for the very first line when given second
shift data (see examples). The first “Interval should report as zero or
null. Since the first run for the shift has no change over time.
The first line for 2nd shift reports 715 minutes when it should report zero.
It is taking the last good of the last run and giving the difference of the
first good of the first run of the shift (4:45 am to 16:40).
Date Shift Process 1st Last Interval
Good Good
4/18/2006 1 2 6:08 9:27 0
4/18/2006 1 2 9:44 11:10 17
4/18/2006 1 2 11:19 12:09 9
4/18/2006 1 2 12:17 13:38 8
4/18/2006 1 2 13:56 15:40 18
4/18/2006 1 2 16:17 16:30 37
Date Shift Process 1st Last Interval
Good Good
4/18/2006 2 2 16:40 18:22 715
4/18/2006 2 2 18:35 21:00 13
4/18/2006 2 2 21:09 23:26 9
4/18/2006 2 2 23:42 2:50 16
4/18/2006 2 2 2:56 4:45 6
Another quirk when open in design view I get an error (syntax error in query
expression ’00:00 AM #,1,0) but no error in SQL view and it does run beside
the above noted problem.
The only obscure items when reading it maybe “process†= the machine used to
run the mold and “fWhoWhenRecoredNumber†= unique identifier for the entire
day and shift.
Actual query used.
SELECT TblWhoWhen.Date, TblWhoWhen.Shift, T.Process, T.FirstGoodPart,
T.LastGoodPart, (SELECT DateDiff("n",NZ(MAX (T1.LastGoodPart +
TblWhoWhen.Date + IIF(TblWhoWhen.Shift = 2 AND T1.LastGoodPart >
#00:00:00#,1,0)),T.FirstGoodPart + TblWhoWhen.Date + IIF(TblWhoWhen.Shift =
2 AND T.FirstGoodPart > #00:00:00#,1,0)), T.FirstGoodPart + TblWhoWhen.Date +
IIF(TblWhoWhen.Shift = 2 AND T.FirstGoodPart > #00:00:00#,1,0))
FROM TblMoldRun T1
WHERE T.fWhoWhenRecoredNumber = T1.fWhoWhenRecoredNumber AND T.Process =
T1.Process AND T1.LastGoodPart < T.FirstGoodPart) AS [Interval]
FROM TblWhoWhen INNER JOIN TblMoldRun AS T ON
TblWhoWhen.WhoWhenRecoredNumber = T.fWhoWhenRecoredNumber
WHERE (((TblWhoWhen.Date)=[Enter Date]) AND ((TblWhoWhen.Shift)=[Enter
Shift]) AND ((T.Process)=[IMM]))
ORDER BY (T.FirstGoodPart + TblWhoWhen.Date + IIF(TblWhoWhen.Shift = 2 AND
T.FirstGoodPart < #06:00:00#,1,0) );
last good of mold “A†to first good part of Mold “Bâ€.
For the most part it works, except for second shift. Second shift runs past
midnight. The query works except for the very first line when given second
shift data (see examples). The first “Interval should report as zero or
null. Since the first run for the shift has no change over time.
The first line for 2nd shift reports 715 minutes when it should report zero.
It is taking the last good of the last run and giving the difference of the
first good of the first run of the shift (4:45 am to 16:40).
Date Shift Process 1st Last Interval
Good Good
4/18/2006 1 2 6:08 9:27 0
4/18/2006 1 2 9:44 11:10 17
4/18/2006 1 2 11:19 12:09 9
4/18/2006 1 2 12:17 13:38 8
4/18/2006 1 2 13:56 15:40 18
4/18/2006 1 2 16:17 16:30 37
Date Shift Process 1st Last Interval
Good Good
4/18/2006 2 2 16:40 18:22 715
4/18/2006 2 2 18:35 21:00 13
4/18/2006 2 2 21:09 23:26 9
4/18/2006 2 2 23:42 2:50 16
4/18/2006 2 2 2:56 4:45 6
Another quirk when open in design view I get an error (syntax error in query
expression ’00:00 AM #,1,0) but no error in SQL view and it does run beside
the above noted problem.
The only obscure items when reading it maybe “process†= the machine used to
run the mold and “fWhoWhenRecoredNumber†= unique identifier for the entire
day and shift.
Actual query used.
SELECT TblWhoWhen.Date, TblWhoWhen.Shift, T.Process, T.FirstGoodPart,
T.LastGoodPart, (SELECT DateDiff("n",NZ(MAX (T1.LastGoodPart +
TblWhoWhen.Date + IIF(TblWhoWhen.Shift = 2 AND T1.LastGoodPart >
#00:00:00#,1,0)),T.FirstGoodPart + TblWhoWhen.Date + IIF(TblWhoWhen.Shift =
2 AND T.FirstGoodPart > #00:00:00#,1,0)), T.FirstGoodPart + TblWhoWhen.Date +
IIF(TblWhoWhen.Shift = 2 AND T.FirstGoodPart > #00:00:00#,1,0))
FROM TblMoldRun T1
WHERE T.fWhoWhenRecoredNumber = T1.fWhoWhenRecoredNumber AND T.Process =
T1.Process AND T1.LastGoodPart < T.FirstGoodPart) AS [Interval]
FROM TblWhoWhen INNER JOIN TblMoldRun AS T ON
TblWhoWhen.WhoWhenRecoredNumber = T.fWhoWhenRecoredNumber
WHERE (((TblWhoWhen.Date)=[Enter Date]) AND ((TblWhoWhen.Shift)=[Enter
Shift]) AND ((T.Process)=[IMM]))
ORDER BY (T.FirstGoodPart + TblWhoWhen.Date + IIF(TblWhoWhen.Shift = 2 AND
T.FirstGoodPart < #06:00:00#,1,0) );