My Brain hurts. Query works in all but one case.

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

 

[Tom Ellison]

Dear Kevin:

You've been busy! OK, I reproduce your query, altered slightly for my own
reading preferences.

SELECT W.Date, W.Shift, T.Process, T.FirstGoodPart, T.LastGoodPart,
(SELECT
DateDiff("n", NZ(MAX(T1.LastGoodPart + W.Date +
IIF(W.Shift = 2 AND T1.LastGoodPart > #00:00:00#, 1, 0)),
T.FirstGoodPart + W.Date +
IIF(W.Shift = 2 AND T.FirstGoodPart > #00:00:00#,1,0)),
T.FirstGoodPart + W.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 W
INNER JOIN TblMoldRun AS T
ON W.WhoWhenRecoredNumber = T.fWhoWhenRecoredNumber
WHERE W.Date = [Enter Date]
AND TblWhoWhen.Shift = [Enter Shift]
AND T.Process = [IMM]
ORDER BY T.FirstGoodPart + W.Date +
IIF(W.Shift = 2 AND T.FirstGoodPart < #06:00:00#, 1, 0);

Here are some observations that might lead to a solution:

1. The LastGoodPart > #00:00:00# would be true for any time not midnight,
right? Is that what you intended. Or should it be at test like
LastGoodPart < #12:00:00# (noon)? I'm thinking you are splitting second
shift into portions in the prior day and in the following day (past
midnight). Right? See the problem with the logic?

2. I see the problem's probable root, and a way to make things easier.
Build a query that builds a FirstGoodPartDT (Date/Time) column by adding the
Date and FirstGoodPart together, adding one day to the date when second
shift and past midnight. Do the same to create a LastGoodPartDT. You can
check these values in that intermediate query. Then build the remainder on
that query. I think you'll be happy with the results.

Please let me know if this helped and if I can be of any other assistance.

Tom Ellison

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

 

[Guest]

Thank you for the insight. I'll spend an hour or two on it tonight.

Dear Kevin:

You've been busy! OK, I reproduce your query, altered slightly for my own
reading preferences.

SELECT W.Date, W.Shift, T.Process, T.FirstGoodPart, T.LastGoodPart,
(SELECT
DateDiff("n", NZ(MAX(T1.LastGoodPart + W.Date +
IIF(W.Shift = 2 AND T1.LastGoodPart > #00:00:00#, 1, 0)),
T.FirstGoodPart + W.Date +
IIF(W.Shift = 2 AND T.FirstGoodPart > #00:00:00#,1,0)),
T.FirstGoodPart + W.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 W
INNER JOIN TblMoldRun AS T
ON W.WhoWhenRecoredNumber = T.fWhoWhenRecoredNumber
WHERE W.Date = [Enter Date]
AND TblWhoWhen.Shift = [Enter Shift]
AND T.Process = [IMM]
ORDER BY T.FirstGoodPart + W.Date +
IIF(W.Shift = 2 AND T.FirstGoodPart < #06:00:00#, 1, 0);

Here are some observations that might lead to a solution:

1. The LastGoodPart > #00:00:00# would be true for any time not midnight,
right? Is that what you intended. Or should it be at test like
LastGoodPart < #12:00:00# (noon)? I'm thinking you are splitting second
shift into portions in the prior day and in the following day (past
midnight). Right? See the problem with the logic?

2. I see the problem's probable root, and a way to make things easier.
Build a query that builds a FirstGoodPartDT (Date/Time) column by adding the
Date and FirstGoodPart together, adding one day to the date when second
shift and past midnight. Do the same to create a LastGoodPartDT. You can
check these values in that intermediate query. Then build the remainder on
that query. I think you'll be happy with the results.

Please let me know if this helped and if I can be of any other assistance.

Tom Ellison

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