Sums in a Union Query

[Jim Pockmire]

I have a union query extracting data from 2 tables. Each SQL select in the
query extracts a product id and sales amount. Is it possible to have the
union query sum by product id? Currently, there is a separate sum be product
id for each of the SQL selects.

 

[John Spencer (MVP)]

Not within the union query itself.

You can use the union query as the basis of a totals query OR you can do the
following in some versions of Access. Try it and see if it works.

UNTESTED SQL statement.

SELECT X.ProductID, Sum(X.SalesAmount) as TotalSales
FROM
(SELECT ProductID, SalesAmount
FROM TableA
UNION
SELECT MyProduct, Amount
FROM TableB) As X
GROUP BY X.ProductID