Simple query question regarding SUM

[diddydustin]

Hey guys,

I have this query:

EMR_SheetsUnionSum:

SELECT Sum(EMR_SheetsUnion.TotalSheets) AS SumOfTotalSheets, EMR_SheetsUnion.Machine, EMR_SheetsUnion.Date, EMR_SheetsUnion.Shift, EMR_SheetsUnion.TotalSheets
FROM EMR_SheetsUnion
GROUP BY EMR_SheetsUnion.Machine, EMR_SheetsUnion.Date, EMR_SheetsUnion.Shift, EMR_SheetsUnion.TotalSheets;


It returns these results:

Machine Date Shift TotalSheets
1224 10/8/2004 1 1000
1224 10/8/2004 1 2000
1224 10/7/2004 1 2000
1224 10/7/2004 2 2000
1224 10/7/2004 3 2000
1225 10/7/2004 3 2000


If you notice, for machine 1224 on Shift 1 on 10/8/2004 there are 2 records, 2000 and 1000 sheets. I have tried everything but cannot get this query to return:

Machine Date Shift TotalSheets
1224 10/8/2004 1 3000
1224 10/7/2004 1 2000
1224 10/7/2004 2 2000
1224 10/7/2004 3 2000
1225 10/7/2004 3 2000

Where these two records are added together to return 3000 sheets.

What do I need to be doing? Thanks


Note:

EMR_SheetsUnion:

SELECT EMR_Graphic_Hour_Detail_Query.[Sum Of Load_Count] AS TotalSheets,
EMR_Graphic_Hour_Detail_Query.Machine, EMR_Graphic_Hour_Detail_Query.Date,
EMR_Graphic_Hour_Detail_Query.Shift
FROM EMR_EmployeeHistory INNER JOIN EMR_Graphic_Hour_Detail_Query ON (EMR_EmployeeHistory.Machine = EMR_Graphic_Hour_Detail_Query.Machine) AND (EMR_EmployeeHistory.Shift = EMR_Graphic_Hour_Detail_Query.Shift) AND (EMR_EmployeeHistory.Date = EMR_Graphic_Hour_Detail_Query.Date)
GROUP BY
EMR_Graphic_Hour_Detail_Query.[Sum Of Load_Count], EMR_Graphic_Hour_Detail_Query.Machine, EMR_Graphic_Hour_Detail_Query.Date, EMR_Graphic_Hour_Detail_Query.Shift;

EMR_Graphic_Hour_Detail_Query:

SELECT DISTINCTROW EMR_Graphic_Hour_Detail.Machine,
Sum(EMR_Graphic_Hour_Detail.Load_Count) AS [Sum Of Load_Count],
EMR_Graphic_Hour_Detail.Date,
EMR_EmployeeHistory.Job_ID,
EMR_EmployeeHistory.Shift
FROM EMR_Graphic_Hour_Detail INNER JOIN EMR_EmployeeHistory ON EMR_Graphic_Hour_Detail.Job_ID = EMR_EmployeeHistory.Job_ID
WHERE
(((EMR_Graphic_Hour_Detail.Job_ID)=[EMR_EmployeeHistory].[Job_ID]))
GROUP BY
EMR_Graphic_Hour_Detail.Machine, EMR_Graphic_Hour_Detail.Date, EMR_EmployeeHistory.Job_ID, EMR_EmployeeHistory.Shift;

 

[PHV]

Get rid of TotalSheets in the GROUP BY clause.

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

 

[diddydustin]

When I do that and try to run the query it tells me :

You tried to execute a query that does not include the specified expression 'TotalSheets' as part of an aggregate function.

Where else can I include TotalSheets in the query?

Thanks
Dustin

 

[PHV]

Try this:
SELECT Sum(EMR_SheetsUnion.TotalSheets) AS SumOfTotalSheets, EMR_SheetsUnion.Machine, EMR_SheetsUnion.Date, EMR_SheetsUnion.Shift
FROM EMR_SheetsUnion
GROUP BY EMR_SheetsUnion.Machine, EMR_SheetsUnion.Date, EMR_SheetsUnion.Shift;


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

 

[diddydustin]

PHV,

I have tried that-- however, it still tells me the same thing:

You tried to execute a query that does not include the specified expression 'TotalSheets' as part of an aggregate function.


Usually when I get this message I just throw the expression in the GROUP BY clause, however, is there some place else I can stick it so the query is happy?

Thank you
Dustin

 

[PHV]

The query I just posted shouldn't tells you this thing as 'TotalSheets' is ONLY part of the Sum aggregate function.

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