SumOfQuantity for Part Numbers in Inventory 1

[rwn]

I have this querty below that works, but it needs to do the SumOfQuantity based on a Part Number vs showing the SumOfQuantity of each StockRoom Code for each Part Number.

SELECT MRPParts.Priority, PartMaster.PartNumber, MRPParts.ISC, MRPParts.OMC, Employees.EmployeeID, Employees.LastName, PartMaster.DescText, PartMaster.DepartmentCode, PartMaster.StockroomCode, InventoryLots.LocationCode, PartMaster.CommodityCode, PartXReference.SupplierID, InventoryLots.Quantity, Sum(InventoryLots.Quantity) AS SumOfQuantity, PartXReference.ApprovedSource
FROM ((MRPParts INNER JOIN (PartMaster LEFT JOIN (CommodityCodes LEFT JOIN Employees ON CommodityCodes.EmployeeID = Employees.EmployeeID) ON PartMaster.CommodityCode = CommodityCodes.CommodityCode) ON MRPParts.PartNumber = PartMaster.PartNumber) LEFT JOIN InventoryLots ON PartMaster.PartNumber = InventoryLots.PartNumber) LEFT JOIN PartXReference ON PartMaster.PartNumber = PartXReference.PartNumber
GROUP BY MRPParts.Priority, PartMaster.PartNumber, MRPParts.ISC, MRPParts.OMC, Employees.EmployeeID, Employees.LastName, PartMaster.DescText, PartMaster.DepartmentCode, PartMaster.StockroomCode, InventoryLots.LocationCode, PartMaster.CommodityCode, PartXReference.SupplierID, InventoryLots.Quantity, MRPParts.MRPParts_PKey, PartXReference.ApprovedSource
HAVING (((MRPParts.Priority)<=5) AND ((PartXReference.ApprovedSource)=-1))
ORDER BY MRPParts.Priority, PartMaster.PartNumber, MRPParts.MRPParts_PKey;

 

[PHV]

SELECT MRPParts.Priority, PartMaster.PartNumber, MRPParts.ISC, MRPParts.OMC, Employees.EmployeeID, Employees.LastName, PartMaster.DescText, PartMaster.DepartmentCode, PartMaster.StockroomCode, InventoryLots.LocationCode, PartMaster.CommodityCode, PartXReference.SupplierID, InventoryLots.Quantity
, [!](SELECT Sum(Quantity) FROM InventoryLots WHERE PartNumber=PartMaster.PartNumber) AS SumOfPNQuantity[/!], PartXReference.ApprovedSource
FROM ((MRPParts INNER JOIN (PartMaster LEFT JOIN (CommodityCodes LEFT JOIN Employees ON CommodityCodes.EmployeeID = Employees.EmployeeID) ON PartMaster.CommodityCode = CommodityCodes.CommodityCode) ON MRPParts.PartNumber = PartMaster.PartNumber) LEFT JOIN InventoryLots ON PartMaster.PartNumber = InventoryLots.PartNumber) LEFT JOIN PartXReference ON PartMaster.PartNumber = PartXReference.PartNumber
[!]WHERE[/!] MRPParts.Priority<=5 AND PartXReference.ApprovedSource=-1
ORDER BY MRPParts.Priority, PartMaster.PartNumber, MRPParts.MRPParts_PKey;


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rwn]

Thanks. I'm getting a syntax error(missing operator in expression).

 

[rwn]

My appologize for my original query had the syntax error. Can you recreate with your recommendation?

SELECT MRPParts.Priority, PartMaster.PartNumber, MRPParts.ISC, MRPParts.OMC, Employees.EmployeeID, Employees.LastName, PartMaster.DescText, PartMaster.DepartmentCode, PartMaster.StockroomCode, PartMaster.CommodityCode, PartXReference.SupplierID, InventoryLots.Quantity, Sum(InventoryLots.Quantity) AS SumOfQuantity, PartXReference.ApprovedSource
FROM ((MRPParts INNER JOIN (PartMaster LEFT JOIN (CommodityCodes LEFT JOIN Employees ON CommodityCodes.EmployeeID = Employees.EmployeeID) ON PartMaster.CommodityCode = CommodityCodes.CommodityCode) ON MRPParts.PartNumber = PartMaster.PartNumber) LEFT JOIN InventoryLots ON PartMaster.PartNumber = InventoryLots.PartNumber) LEFT JOIN PartXReference ON PartMaster.PartNumber = PartXReference.PartNumber
GROUP BY MRPParts.Priority, PartMaster.PartNumber, MRPParts.ISC, MRPParts.OMC, Employees.EmployeeID, Employees.LastName, PartMaster.DescText, PartMaster.DepartmentCode, PartMaster.StockroomCode, PartMaster.CommodityCode, PartXReference.SupplierID, InventoryLots.Quantity, MRPParts.MRPParts_PKey, PartXReference.ApprovedSource
HAVING (((MRPParts.Priority)<=5) AND ((PartXReference.ApprovedSource)=-1))
ORDER BY MRPParts.Priority, PartMaster.PartNumber, MRPParts.MRPParts_PKey;

 

[rwn]

Please disregard above. The syntax was all in the copy and the paste and just has to be reformated.

The query runs fine, but doesn't group by part numbers. It still shows a part number multi times if it is in mult Locations in the Inventory.Lots Table.

 

[PHV]

And this ?
SELECT MRPParts.Priority, PartMaster.PartNumber, MRPParts.ISC, MRPParts.OMC, Employees.EmployeeID, Employees.LastName, PartMaster.DescText, PartMaster.DepartmentCode, PartMaster.StockroomCode, PartMaster.CommodityCode, PartXReference.SupplierID, Sum(InventoryLots.Quantity) AS SumOfQuantity, PartXReference.ApprovedSource
FROM ((MRPParts INNER JOIN (PartMaster LEFT JOIN (CommodityCodes LEFT JOIN Employees ON CommodityCodes.EmployeeID = Employees.EmployeeID) ON PartMaster.CommodityCode = CommodityCodes.CommodityCode) ON MRPParts.PartNumber = PartMaster.PartNumber) LEFT JOIN InventoryLots ON PartMaster.PartNumber = InventoryLots.PartNumber) LEFT JOIN PartXReference ON PartMaster.PartNumber = PartXReference.PartNumber
WHERE MRPParts.Priority<=5 AND PartXReference.ApprovedSource=-1
GROUP BY MRPParts.Priority, PartMaster.PartNumber, MRPParts.ISC, MRPParts.OMC, Employees.EmployeeID, Employees.LastName, PartMaster.DescText, PartMaster.DepartmentCode, PartMaster.StockroomCode, PartMaster.CommodityCode, PartXReference.SupplierID, PartXReference.ApprovedSource
ORDER BY MRPParts.Priority, PartMaster.PartNumber;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rwn]

Thank you very much!!!!!