Union Query with Totals

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I am having problems adding the values for the results of
two querries using the union query. Any solution? See
below:

SELECT AmdByCustCom.PartID AS PARTID, Sum
(AmdByCustCom.AMD) AS TotalAmd
FROM AmdByCustCom
WHERE PARTID = [ENTER A PART ID]
GROUP BY PARTID

UNION ALL SELECT AmdByCustIndy.PartID AS PARTID, Sum
(AmdByCustIndy.AMD) AS TotalAmd
FROM AmdByCustIndy
WHERE PARTID = [ENTER A PART ID]
GROUP BY PARTID;
 
Dear Ray:

I'm guessing at your problem, but I think the solution may be:

SELECT PARTID, SUM(AMD) AS TotalAmd FROM (
SELECT PartID AS PARTID, AMD
FROM AmdByCustCom
UNION ALL
SELECT PartID AS PARTID, AMD
FROM AmdByCustIndy
) x
WHERE PARTID = [ENTER A PART ID]
GROUP BY PARTID

Based on my assumptions of what you want, the above makes a UNION of
the raw data, then aggregates that. It's the same thing you had done,
but with the steps of UNION and aggregation reversed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
If you meant adding the 2 Sums already in your Query, you can use the posted
SQL String as the SubQuery of the summation Query like: (untested):

SELECT PartID, Sum(TotalAmd)
FROM
(
SELECT AmdByCustCom.PartID,
Sum(AmdByCustCom.AMD) AS TotalAmd
FROM AmdByCustCom
WHERE PartID = [ENTER A PART ID]
GROUP BY PartID

UNION ALL
SELECT AmdByCustIndy.PartID,
Sum(AmdByCustIndy.AMD)
FROM AmdByCustIndy
WHERE PartID = [ENTER A PART ID]
GROUP BY PartID
)
GROUP BY PartID;

Alternatively, you can do simple union without sums and then do one sum at
the end like:

SELECT PartID, Sum(AMD)
FROM
(
SELECT ABCC.PartID, ABCC.AMD
FROM AmdByCustCom AS ABCC
WHERE ABCC.PartID = [ENTER A PART ID]

UNION ALL
SELECT ABCI.PartID, ABCI.AMD
FROM AmdByCustIndy As ABCI
WHERE ABCI.PartID = [ENTER A PART ID]
)
GROUP BY PartID;
 
Thanks for both responses. Both worked.
Ray
-----Original Message-----
If you meant adding the 2 Sums already in your Query, you can use the posted
SQL String as the SubQuery of the summation Query like: (untested):

SELECT PartID, Sum(TotalAmd)
FROM
(
SELECT AmdByCustCom.PartID,
Sum(AmdByCustCom.AMD) AS TotalAmd
FROM AmdByCustCom
WHERE PartID = [ENTER A PART ID]
GROUP BY PartID

UNION ALL
SELECT AmdByCustIndy.PartID,
Sum(AmdByCustIndy.AMD)
FROM AmdByCustIndy
WHERE PartID = [ENTER A PART ID]
GROUP BY PartID
)
GROUP BY PartID;

Alternatively, you can do simple union without sums and then do one sum at
the end like:

SELECT PartID, Sum(AMD)
FROM
(
SELECT ABCC.PartID, ABCC.AMD
FROM AmdByCustCom AS ABCC
WHERE ABCC.PartID = [ENTER A PART ID]

UNION ALL
SELECT ABCI.PartID, ABCI.AMD
FROM AmdByCustIndy As ABCI
WHERE ABCI.PartID = [ENTER A PART ID]
)
GROUP BY PartID;


--
HTH
Van T. Dinh
MVP (Access)



I am having problems adding the values for the results of
two querries using the union query. Any solution? See
below:

SELECT AmdByCustCom.PartID AS PARTID, Sum
(AmdByCustCom.AMD) AS TotalAmd
FROM AmdByCustCom
WHERE PARTID = [ENTER A PART ID]
GROUP BY PARTID

UNION ALL SELECT AmdByCustIndy.PartID AS PARTID, Sum
(AmdByCustIndy.AMD) AS TotalAmd
FROM AmdByCustIndy
WHERE PARTID = [ENTER A PART ID]
GROUP BY PARTID;
Click to expand...


.
Click to expand...
 
Back
Top