Union Query - adding sums results from queries 'Unioned'

[delorfra]

Hi,
I want to divide the monthly total number of scrap which I get by summing scrap records from one table by monthly quantities delivered which I get from the deliveries Table.
But sometimes there is no value for a specific month as no parts were scrapped.
To avoid this, I tried to add the monthly total scrap table to a table containing zeros for each month thanks to an Union query so that no month will be missing.
The trouble is that the query won't add zeros to values leaving me with two records for months with scraps.

In the second query after the UNION instruction I have dropped the Aliases ('AS'). The queries have sums in them and GROUP BY so that It should sum results from both queries.

ex :
SELECT PLANT_No, YEAR, MONTH, SUM(NoPARTS) AS TOTSCRAP FROM SCRAPS
GROUP BY PLANT_No, YEAR, MONTH
UNION PLANT_No, YEAR, MONTH, SUM(ZEROVALUE) FROM SCRAPS_INI
GROUP BY PLANT_No, YEAR, MONTH

Thanks.

 

[MichaelRed]

SELECT PLANT_No, YEAR, MONTH, 0 AS TOTSCRAP FROM SCRAPS
GROUP SELECT BY PLANT_No, YEAR, MONTH
UNION PLANT_No, YEAR, MONTH, 0 FROM SCRAPS_INI
GROUP BY PLANT_No, YEAR, MONTH


Try this for the UNION query. Then do your Sum on these Results. Finally, do whatever with the sum and the deliveries.

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Try this for the Aggregate Query:

SELECT qryUnionW_Zero.PLANT_No, qryUnionW_Zero.YEAR, qryUnionW_Zero.MONTH, Sum(qryUnionW_Zero.TOTSCRAP) AS TotScrap
FROM qryUnionW_Zero
GROUP BY qryUnionW_Zero.PLANT_No, qryUnionW_Zero.YEAR, qryUnionW_Zero.MONTH;

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over