Union and Sum all in one query

[joewilly1]

Seems similar questions have been asked but I haven't found the exact answer to my problem. I have two tables with the same exact structure that were imported from Excel. The fields would be something like Product, Jan, Feb, etc. There's a table that specifically has sales for Jan, a separate one for Feb and so on. I want a table that shows sales for a product for each month. When I do a union query I get a table that has record for Product A with Jan sales and zeros for the other months. There's another record for Product A with Feb sales and zeros in the other months and so on. I want one record for Product A with sales for each month. It's pretty easy to do a union query and then a sum query but I was wondering if it could be done in one query instead of two.

[Robeen]

I think you will need a CrossTab query to make your data come out the way you want.

Best solution I can come up with is a 2-step solution like this:
1. UNION ALL Query ["QrySales_A_B_C"] with data from each of the tables grouped:

SELECT "1_Jan" AS SaleMonth, Sales_A.Product, Sum(Sales_A.Amount) AS Amt
FROM Sales_A
GROUP BY "1_Jan", Sales_A.Product
UNION ALL
SELECT "2_Feb" AS SaleMonth, Sales_B.Product, Sum(Sales_B.Amount) AS Amt
FROM Sales_B
GROUP BY "2_Feb", Sales_B.Product
UNION ALL
SELECT "3_Mar" AS SaleMonth, Sales_C.Product, Sum(Sales_C.Amount) AS Amt
FROM Sales_C
GROUP BY "3_Mar", Sales_C.Product;

2. CrossTab Query using the above query to get your data looking the way you need it to:

TRANSFORM Sum(QrySales_A_B_C.[Amt]) AS SumOfAmt
SELECT QrySales_A_B_C.[Product]
FROM QrySales_A_B_C
GROUP BY QrySales_A_B_C.[Product]
PIVOT QrySales_A_B_C.[SaleMonth];

* I know using "1_Jan", 2_Feb" . . . is probably not the most elegant way to do this but I can't think of another way without addin steps.
** I used the Query Wizard to create a CrossTab Query.

I added screenshots to clarify . . .

I hope this helps!!