Jolien .A Jolien .A - 4 months ago 14
SQL Question

MS Access and SQL: how to sum the output of 10+ queries

enter image description here

Added error message

I have 19 queries that each calculate a value. I now want to add all these values together.

This is the huge query

SELECT qryNewAluminumMaterialCostPerUnit.NewAluminumMaterialCost + qryNewChromeMaterialCostPerUnit.NewChromeMaterialCost + qryNewCobaltMaterialCostPerUnit.NewCobaltMaterialCost + qryNewCopperMaterialCostPerUnit.NewCopperMaterialCost + qryNewCRSBenchmarkerEofMSMaterialCostPerUnit.NewCRSBenchmarkerEofMSMaterialCost + qryNewCrudeOilMaterialCostPerUnit.NewCrudeOilMaterialCost + qryNewIronScrap1ChicagoBushMaterialCostPerUnit.NewIronScrap1ChicagoBushMaterialCost + qryNewMWAluminumMaterialCostPerUnit.NewMWAluminumMaterialCost + qryNewNaturalGasMaterialCostPerUnit.NewNaturalGasMaterialCost + qryNewNickelMaterialCostPerUnit.NewNickelMaterialCost + qryNewOilMaterialCostPerUnit.NewOilMaterialCost + qryNewOtherMaterialCostPerUnit.NewOtherMaterialCost + qryNewPlastic1MaterialCostPerUnit.NewPlastic1MaterialCost + qryNewPlastic2MaterialCostPerUnit.NewPlastic2MaterialCost + qryNewPlastic3MaterialCostPerUnit.NewPlastic3MaterialCost + qryNewSilverMaterialCostPerUnit.NewSilverMaterialCost + qryNewTheoretical301MaterialCostPerUnit.NewTheoretical301MaterialCost + qryNewTheoretical304MaterialCostPerUnit.NewTheoretical304MaterialCost + qryNewZincMaterialCostPerUnit.NewZincMaterialCost
FROM qryNewAluminumMaterialCostPerUnit, qryNewChromeMaterialCostPerUnit, qryNewCobaltMaterialCostPerUnit, qryNewCopperMaterialCostPerUnit, qryNewCRSBenchmarkerEofMSMaterialCostPerUnit, qryNewCrudeOilMaterialCostPerUnit, qryNewIronScrap1ChicagoBushMaterialCostPerUnit, qryNewMWAluminumMaterialCostPerUnit, qryNewNaturalGasMaterialCostPerUnit, qryNewNickelMaterialCostPerUnit, qryNewOilMaterialCostPerUnit, qryNewOtherMaterialCostPerUnit, qryNewPlastic1MaterialCostPerUnit, qryNewPlastic2MaterialCostPerUnit, qryNewPlastic3MaterialCostPerUnit, qryNewSilverMaterialCostPerUnit, qryNewTheoretical301MaterialCostPerUnit, qryNewTheoretical304MaterialCostPerUnit, qryNewZincMaterialCostPerUnit;


I get the error that there isn't enough room. Is there a more simple way to add all queries' output than the above format?

NOte: all queries return one single record, one single value.

Answer
SELECT q1 + q2 + q3 + .... q19
FROM q1, q2, q3, ... q19

If each query returns a single record, then this should work fine. If it doesn't then you are getting a Cartesian product of all the tables... which fill up ram in a heart beat. If your queries have many records and many fields, then this doesn't even make sense to begin with.

Perhaps Join your queries, or Union your result sets and then sum. Something like:

   SELECT sum(f1) 
   FROM
       (
            SELECT f1 FROM q1
            UNION ALL
            SELECT f1 FROM q2
            UNION ALL
            SELECT f1 FROM q3
       )as unionSubQuery
Comments