diddydustin
Programmer
Hey guys,
I have three queries.
EMR_TimeUnion
EMR_SheetsUnion
EMR_UPSH
EMR_TimeUnion:
SELECT EMR_TotalGraphicTime.[Sum Of Time_Total], EMR_TotalGraphicTime.Time_Code,
EMR_TimeCodeList.Desc,
EMR_Base.Machine,
EMR_Base.Date,
EMR_Base.Shift
FROM (EMR_Base INNER JOIN EMR_TotalGraphicTime ON EMR_Base.Time_ID = EMR_TotalGraphicTime.Time_ID) INNER JOIN EMR_TimeCodeList ON EMR_TotalGraphicTime.Time_Code = EMR_TimeCodeList.Code
WHERE (((EMR_TotalGraphicTime.Time_Code)=[EMR_TimeCodeList].
I have three queries.
EMR_TimeUnion
EMR_SheetsUnion
EMR_UPSH
EMR_TimeUnion:
SELECT EMR_TotalGraphicTime.[Sum Of Time_Total], EMR_TotalGraphicTime.Time_Code,
EMR_TimeCodeList.Desc,
EMR_Base.Machine,
EMR_Base.Date,
EMR_Base.Shift
FROM (EMR_Base INNER JOIN EMR_TotalGraphicTime ON EMR_Base.Time_ID = EMR_TotalGraphicTime.Time_ID) INNER JOIN EMR_TimeCodeList ON EMR_TotalGraphicTime.Time_Code = EMR_TimeCodeList.Code
WHERE (((EMR_TotalGraphicTime.Time_Code)=[EMR_TimeCodeList].
Code:
))
GROUP BY
EMR_Base.Date,
EMR_Base.Shift,
EMR_Base.Machine,
EMR_TotalGraphicTime.[Sum Of Time_Total], EMR_TotalGraphicTime.Time_Code,
EMR_TimeCodeList.Desc;
EMR_SheetsUnion:
SELECT EMR_Graphic_Hour_Detail_JobID.[Sum Of Load_Count] AS TotalSheets,
EMR_Base.Date,
EMR_Base.Shift,
EMR_Base.Machine
FROM EMR_Base INNER JOIN EMR_Graphic_Hour_Detail_JobID ON EMR_Base.Job_ID = EMR_Graphic_Hour_Detail_JobID.Job_ID
GROUP BY EMR_Graphic_Hour_Detail_JobID.[Sum Of Load_Count],
EMR_Base.Date,
EMR_Base.Shift,
EMR_Base.Machine;
EMR_UPSH:
SELECT EMR_SheetsUnion.TotalSheets, EMR_TimeUnionNoCode.Machine,
EMR_TimeUnionNoCode.Date,
EMR_TimeUnionNoCode.Shift, Sum([Sum Of Time_Total])/3600 AS Expr1
FROM EMR_SheetsUnion,
EMR_TimeUnionNoCode
GROUP BY EMR_SheetsUnion.TotalSheets, EMR_TimeUnionNoCode.Machine,
EMR_TimeUnionNoCode.Date,
EMR_TimeUnionNoCode.Shift;
EMR_UPSH uses EMR_TimeUnionNoCode, which is:
EMR_TimeUnionNoCode:
SELECT EMR_TotalGraphicTime.[Sum Of Time_Total], EMR_Base.Machine,
EMR_Base.Date,
EMR_Base.Shift
FROM EMR_Base INNER JOIN EMR_TotalGraphicTime ON EMR_Base.Time_ID = EMR_TotalGraphicTime.Time_ID
GROUP BY
EMR_TotalGraphicTime.[Sum Of Time_Total],
EMR_Base.Machine,
EMR_Base.Date,
EMR_Base.Shift;
EMR_TimeUnion returns results like:
(Note Time_Total is in seconds)
Machine Sum Of Time_Total Time_Code Desc Date Shift
1224 3600 900 Makeready 9/29/2004 1
1224 25200 100 Run Time 9/29/2004 1
1225 7200 46 Spray Powder 9/29/2004 1
1225 21600 100 Run Time 9/29/2004 1
1224 28800 900 Makeready 9/29/2004 2
1224 7200 900 Makeready 9/29/2004 3
1224 21600 100 Run Time 9/29/2004 3
1224 3600 900 Makeready 9/30/2004 1
EMR_SheetsUnion returns results like:
TotalSheets Date Shift Machine
2000 9/30/2004 1 1224
5000 9/29/2004 1 1224
5000 9/29/2004 1 1225
5000 9/29/2004 3 1224
10000 9/29/2004 2 1224
However, EMR_UPSH returns data like:
TotalSheets Machine Date Shift Expr1
2000 1224 9/29/2004 1 8
2000 1224 9/29/2004 2 8
2000 1224 9/29/2004 3 8
2000 1224 9/30/2004 1 1
2000 1225 9/29/2004 1 8
5000 1224 9/29/2004 1 24
5000 1224 9/29/2004 2 24
5000 1224 9/29/2004 3 24
5000 1224 9/30/2004 1 3
5000 1225 9/29/2004 1 24
10000 1224 9/29/2004 1 8
10000 1224 9/29/2004 2 8
10000 1224 9/29/2004 3 8
10000 1224 9/30/2004 1 1
10000 1225 9/29/2004 1 8
If you notice, the data does not match up between EMR_SheetsUnion and the sheets returned in EMR_UPSH. Shift 3 on 9/29/2004 on Machine 1224 shows 7000 total sheets returned, however, EMR_SheetsUnion only shows 5000.
What am I doing wrong in the query that returns these results? EMR_UPSH basically takes the total sheets for a shift/machine/date and divides it by the total hours.
Thanks
Dustin