Summarizing quantities by Part Number

  • Thread starter Thread starter Deb
  • Start date Start date
D

Deb

This query is working, but I'd like to "summarize" the quantities by
"PartNo". How do I go about doing that?

SELECT DISTINCTROW sqry_Inventory.Program, sqry_Inventory.Division,
sqry_Inventory.PartNo, sqry_Inventory.Serial, sqry_Inventory.Building,
Sum(sqry_Inventory.[Updated Qty]) AS [Sum Of Updated Qty],
Sum(sqry_Inventory.UnitCost) AS [Sum Of UnitCost]
FROM sqry_Inventory
WHERE (((sqry_Inventory.Program)=[Enter Program]))
GROUP BY sqry_Inventory.Program, sqry_Inventory.Division,
sqry_Inventory.PartNo, sqry_Inventory.Serial, sqry_Inventory.Building
ORDER BY sqry_Inventory.Program, sqry_Inventory.Division,
sqry_Inventory.PartNo;
 
How about:

SELECT sqry_Inventory.PartNo,
Sum(sqry_Inventory.[Updated Qty]) AS [Sum Of Updated Qty],
Sum(sqry_Inventory.UnitCost) AS [Sum Of UnitCost]
FROM sqry_Inventory
WHERE (((sqry_Inventory.Program)=[Enter Program]))
GROUP BY sqry_Inventory.PartNo;
 
Thanks. I'll give it a try!

Duane Hookom said:
How about:

SELECT sqry_Inventory.PartNo,
Sum(sqry_Inventory.[Updated Qty]) AS [Sum Of Updated Qty],
Sum(sqry_Inventory.UnitCost) AS [Sum Of UnitCost]
FROM sqry_Inventory
WHERE (((sqry_Inventory.Program)=[Enter Program]))
GROUP BY sqry_Inventory.PartNo;

--
Duane Hookom
Microsoft Access MVP


Deb said:
This query is working, but I'd like to "summarize" the quantities by
"PartNo". How do I go about doing that?

SELECT DISTINCTROW sqry_Inventory.Program, sqry_Inventory.Division,
sqry_Inventory.PartNo, sqry_Inventory.Serial, sqry_Inventory.Building,
Sum(sqry_Inventory.[Updated Qty]) AS [Sum Of Updated Qty],
Sum(sqry_Inventory.UnitCost) AS [Sum Of UnitCost]
FROM sqry_Inventory
WHERE (((sqry_Inventory.Program)=[Enter Program]))
GROUP BY sqry_Inventory.Program, sqry_Inventory.Division,
sqry_Inventory.PartNo, sqry_Inventory.Serial, sqry_Inventory.Building
ORDER BY sqry_Inventory.Program, sqry_Inventory.Division,
sqry_Inventory.PartNo;
Click to expand...
Click to expand...
 
This query is working, but I'd like to "summarize" the quantities by
"PartNo". How do I go about doing that?
Click to expand...

If you mean that you want PartNo to be the grouping level, without breaking it
down by serial or building, just don't include those fields in the query:


SELECT DISTINCTROW sqry_Inventory.Program, sqry_Inventory.Division,
sqry_Inventory.PartNo,
Sum(sqry_Inventory.[Updated Qty]) AS [Sum Of Updated Qty],
Sum(sqry_Inventory.UnitCost) AS [Sum Of UnitCost]
FROM sqry_Inventory
WHERE (((sqry_Inventory.Program)=[Enter Program]))
GROUP BY sqry_Inventory.Program, sqry_Inventory.Division,
sqry_Inventory.PartNo
ORDER BY sqry_Inventory.Program, sqry_Inventory.Division,
sqry_Inventory.PartNo;
 
Back
Top