Help with Group By

D

DawnTreader

Hello All

i have a question about group by. do i need to do this:

SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"1000 Hr Maintenance" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[1000HrMaint])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"5000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[5000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"10000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[10000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"15000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[15000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"20000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[20000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"25000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[25000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
ORDER BY Interval;

like this, or can i do one group by at the end on the Union?

any and all help appreciated. :)
 
D

DawnTreader

Darn...

i guess i should mention that this was working well before i put in the
sum()'s and the group by lines.

it used to look like this:

SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
qryrptSerialCBARPartList.PartBlockQTY,
qryrptSerialCBARPartList.PartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"1000 Hr Maintenance" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[1000HrMaint])=True))
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
qryrptSerialCBARPartList.PartBlockQTY,
qryrptSerialCBARPartList.PartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"5000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[5000HrRebuild])=True))
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
qryrptSerialCBARPartList.PartBlockQTY,
qryrptSerialCBARPartList.PartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"10000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[10000HrRebuild])=True))
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
qryrptSerialCBARPartList.PartBlockQTY,
qryrptSerialCBARPartList.PartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"15000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[15000HrRebuild])=True))
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
qryrptSerialCBARPartList.PartBlockQTY,
qryrptSerialCBARPartList.PartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"20000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[20000HrRebuild])=True))
UNION ALL SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
qryrptSerialCBARPartList.PartBlockQTY,
qryrptSerialCBARPartList.PartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"25000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[25000HrRebuild])=True))
ORDER BY Interval;

so is there something i am doing wrong? the query seems to stall...

DawnTreader said:
Hello All

i have a question about group by. do i need to do this:

SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"1000 Hr Maintenance" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[1000HrMaint])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"5000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[5000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"10000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[10000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"15000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[15000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"20000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[20000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"25000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[25000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
ORDER BY Interval;

like this, or can i do one group by at the end on the Union?

any and all help appreciated. :)
Click to expand...
 
J

John Spencer

You have to group by the interval also

SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"1000 Hr Maintenance" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[1000HrMaint])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue, "1000 Hr Maintenance"

UNION ALL

You should be able to use your original query as the source for a totals
query. The query would look like the following.

SELECT ProductId, IMWPNID, PartDesc, UnitPrice, SkidValue, Interval
, SUM(PartBlockQTY) as SumBlockQty
, SUM(PartSkidQTY) as SumSkidQty
FROM qNamedUnionQuery
GROUP BY ProductId, IMWPNID, PartDesc, UnitPrice, SkidValue, Interval


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hello All

i have a question about group by. do i need to do this:

SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"1000 Hr Maintenance" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[1000HrMaint])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"5000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[5000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"10000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[10000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"15000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[15000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"20000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[20000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"25000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[25000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
ORDER BY Interval;

like this, or can i do one group by at the end on the Union?

any and all help appreciated. :)
Click to expand...
 
D

DawnTreader

Hello John

Nice. i wasnt sure if i should do the intervals too. should have err'ed on
the side of caution i guess. :)

John Spencer said:
You have to group by the interval also

SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"1000 Hr Maintenance" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[1000HrMaint])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue, "1000 Hr Maintenance"

UNION ALL

You should be able to use your original query as the source for a totals
query. The query would look like the following.

SELECT ProductId, IMWPNID, PartDesc, UnitPrice, SkidValue, Interval
, SUM(PartBlockQTY) as SumBlockQty
, SUM(PartSkidQTY) as SumSkidQty
FROM qNamedUnionQuery
GROUP BY ProductId, IMWPNID, PartDesc, UnitPrice, SkidValue, Interval


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hello All

i have a question about group by. do i need to do this:

SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"1000 Hr Maintenance" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[1000HrMaint])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"5000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[5000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"10000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[10000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"15000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[15000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL
SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"20000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[20000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
UNION ALL SELECT
qryrptSerialCBARPartList.ProductID,
qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc,
Sum(qryrptSerialCBARPartList.PartBlockQTY) AS TPartBlockQTY,
Sum(qryrptSerialCBARPartList.PartSkidQTY) AS TPartSkidQTY,
qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue,
"25000 Hr Rebuild" as Interval
FROM
qryrptSerialCBARPartList
WHERE
(((qryrptSerialCBARPartList.[25000HrRebuild])=True))
GROUP BY
qryrptSerialCBARPartList.ProductID, qryrptSerialCBARPartList.IMWPNID,
qryrptSerialCBARPartList.PartDesc, qryrptSerialCBARPartList.UnitPrice,
qryrptSerialCBARPartList.SkidValue
ORDER BY Interval;

like this, or can i do one group by at the end on the Union?

any and all help appreciated. :)
Click to expand...
Click to expand...
 
D

DawnTreader

Hello again

one question about the totals...
You should be able to use your original query as the source for a totals
query. The query would look like the following.

SELECT ProductId, IMWPNID, PartDesc, UnitPrice, SkidValue, Interval
, SUM(PartBlockQTY) as SumBlockQty
, SUM(PartSkidQTY) as SumSkidQty
FROM qNamedUnionQuery
GROUP BY ProductId, IMWPNID, PartDesc, UnitPrice, SkidValue, Interval
Click to expand...

so you are suggesting another query on top of the union one, instead doing
it in my union?

i already have 3 layers of queries below the massive union one that i have
posted. the first level gets the productid and filters all the next level
queries to one product. the 2nd level of queries finds 2 different groups of
parts and lists them. the 3rd level is a union query that takes the 2 2nd
level queries and joins them if one of the lists doesnt have something that
the other does. then the massive union query rearranges the layout so that
the parts are grouped into thier rebuild intervals.

i was thinking about seeing if i could move the grouping one level down to
the 3rd level union query. but i am already getting problems where access is
complaining about too many databases open.

this query i am working on is for a report that will open from the main form
of my app and the main form has a lot of "recordsets" in combos, list boxes
and subforms. i am concerned about the overall efficiency of the whole query
as well.

any suggestions and help appreciated. :)
 
D

DawnTreader

Hello Again

i have another question, something i thought about to do this differently.

i need this query for a report that i want to generate.

i could break this massive query in to 5 seperate small queries and do sub
reports couldnt i?

and if i do is it more efficient?
 
J

John Spencer

Well, I wasn't saying that you should add another query on top. I was saying
that it is a possibility to do this another way.

If you are having performance problems, you can experiment and see which way
is most efficient or just plain works.

And the same answer applies to breaking this down into sub-reports. One way
might be more efficient then the other. I tend to avoid sub-reports, but at
times they are the best solution - especially if the sub-report is not linked
directly to the data in the main report.

For example, if I want to generate summary data in the report footer a
subreport may be the best answer.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Heavy data munching - is there a more efficient way of doing this? 8
minimize repeating query criteria & generating monthly YTD totals 1
Union Query by Month 3
Union query not displaying select statement 1
Subquery group by 4
Sorting a Union Query by Sum, Where to put Desc? 9
System Resource Exceeded/Query Too Complex 4
Help combining queries 2

Top