Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem combining two queries -- data is wrong

Status
Not open for further replies.

diddydustin

Programmer
Jul 30, 2002
181
US
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].
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
 
You have a lot of GROUP BY clauses without any aggregate function in the SELECT list nor HAVING clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
I'm still a little confused-- that doesn't really help me with my problem. What should the SELECT or WHERE clause be in order to get the correct data?

Dustin
 
what results do you get if you remove the GROUP BY clause from all the queries EXCEPT EMR_UPSH?

Leslie
 
The question was:
why using GROUP BY without aggregate function, like Sum or Count ?
Have you tried this ?
EMR_UPSH:
SELECT Sum(EMR_SheetsUnion.TotalSheets) As 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_TimeUnionNoCode.Machine,
EMR_TimeUnionNoCode.Date,
EMR_TimeUnionNoCode.Shift;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Trying that query returns:

TotalSheets Machine Date Shift Expr1
54000 1224 9/29/2004 1 40
27000 1224 9/29/2004 2 40
54000 1224 9/29/2004 3 40
27000 1224 9/30/2004 1 5
54000 1225 9/29/2004 1 40


As you can see from the SheetsUnion, the total sheets returned from EMR_UPSH is much much higher then it should be. What can I do to get this value to be correct? I think this was my problem initially

Dustin
 
Seems a lack of jointure ...
SELECT Sum(EMR_SheetsUnion.TotalSheets) As TotalSheets, EMR_TimeUnionNoCode.Machine,
EMR_TimeUnionNoCode.Date,
EMR_TimeUnionNoCode.Shift,
Sum([Sum Of Time_Total])/3600 AS Expr1
FROM EMR_SheetsUnion INNER JOIN EMR_TimeUnionNoCode
ON EMR_SheetsUnion.Machine=EMR_TimeUnionNoCode.Machine
AND EMR_SheetsUnion.Date=EMR_TimeUnionNoCode.Date
AND EMR_SheetsUnion.Shift=EMR_TimeUnionNoCode.Shift
GROUP BY
EMR_TimeUnionNoCode.Machine,
EMR_TimeUnionNoCode.Date,
EMR_TimeUnionNoCode.Shift;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Okay, the data looks much better but is still not matching:

EMR_SheetsUnion returns:

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


EMR_UPSH returns:

TotalSheets Machine Date Shift Expr1
10000 1224 9/29/2004 1 8
10000 1224 9/29/2004 2 8
10000 1224 9/29/2004 3 8
2000 1224 9/30/2004 1 1
10000 1225 9/29/2004 1 8

As you can see, the TotalSheets are different from each.

I am using the following for EMR_UPSH:

SELECT Sum(EMR_SheetsUnion.TotalSheets) As TotalSheets, EMR_TimeUnionNoCode.Machine,
EMR_TimeUnionNoCode.Date,
EMR_TimeUnionNoCode.Shift,
Sum([Sum Of Time_Total])/3600 AS Expr1
FROM EMR_SheetsUnion INNER JOIN EMR_TimeUnionNoCode
ON EMR_SheetsUnion.Machine=EMR_TimeUnionNoCode.Machine
AND EMR_SheetsUnion.Date=EMR_TimeUnionNoCode.Date
AND EMR_SheetsUnion.Shift=EMR_TimeUnionNoCode.Shift
GROUP BY
EMR_TimeUnionNoCode.Machine,
EMR_TimeUnionNoCode.Date,
EMR_TimeUnionNoCode.Shift;

Thanks
Dustin
 
Sorry, need to bump this up because I am pressed to figure out why this is wrong and can't seem to do so!!

Dustin
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top