Sum After a UNIONALL

[jeffbruce]

Hi everybody,

Here's the code that I'm working on in an MS Access query:

SELECT Year, plantCode, FreightCost FROM qryCorrectFreightCost
UNION ALL
SELECT Year, plantCode, FreightCost FROM qryOtherFreightSum
GROUP BY plantCode, Year, FreightCost;

This works fine, but afterward I want to Sum FreightCost and GROUP BY plantCode. How would I go about doing this in the same query?

 

[DCrake]

Why not just create a new query that references this union query to get the results you require?

 

[jeffbruce]

The reason I'm hesitant is because I'm avoiding building unnecessary queries. The database is large as it is, and it gets needlessly complicated as the number of queries increases. On that note, I found on another forum the following solution:

Try:

SELECT A, SUM(B) FROM

(
SELECT A, B
FROM X
UNION ALL
SELECT A,B
FROM Y ) C

GROUP BY A ;

I've tried implementing this and it is not working; I'm getting the error message "You tried to execute a query that does not include the specified expression 'FreightCost' as part of an aggregate function." Also I'm wondering what must be substituted for "C" in the above solution... I figure it should be the query that runs this SQL code.

 

[jeffbruce]

I got it, never mind. The template provided does work, I had a GROUP BY command buried in the bracket loop which is why it was failing.

 

[DCrake]

SELECT Year, plantCode, Sum(FreightCost) As Freight FROM qryCorrectFreightCost
Group By plantcode, Year
UNION ALL 
SELECT Year, plantCode, Sum(FreightCost) As Freight FROM qryOtherFreightSum
GROUP BY plantCode, Year

Try this. Not tested as I do not have the base tables.

David

 

[jeffbruce]

David,

Unfortunately, the code you posted only works partially for my cause. Everything works fine except for the fact that the end result (after unioning the two queries) is not grouped by distinct plantCodes/Year. The code I posted below works just fine, but now I am working on making it a maketable query. Any suggestions on how to do so? I know to use the INTO command, but not sure of the structure (i.e. where to put the INTO command, without fudging up the current code module).

It seems with SQL I'm always flabbergasted as to the order of which the commands should be presented, and what shapes/structures they can take on. Does anyone know of a good tutorial for the SQL language that focuses on the general structure of the language rather than repeated examples of specific commands?

 

[jeffbruce]

Got it. I modified my code to look like this, just in case anyone is interested...

Code:
SELECT qrySumFreight.plantCode, qrySumFreight.Year, Sum(qrySumFreight.FreightCost) AS FreightCost INTO tblFreightSum
FROM (SELECT plantCode, Year, FreightCost FROM qryCorrectFreightCost
UNION ALL SELECT plantCode,Year, FreightCost FROM qryOtherFreightSum) AS qrySumFreight
GROUP BY qrySumFreight.plantCode, qrySumFreight.Year;

I'm still curious to hear whether any of you out there know some good SQL tutorials that focus on structure of the language, as opposed to bombarding the learner with an endless stream of examples.

Anyway, thanks for the help.