I have these two queries that work fine and need to combine the second query to the first query if the PODetail.PartNumber = WOShortages.PartNumber.
FIRST QUERY:
SELECT WOShortages.WONumber, WOShortages.PartNumber, WOShortages.TransactionDate, WOShortages.Quantity, WOShortages.UOM, L.NettableQuantity, P.DescText AS PartDescription
FROM (WOShortages LEFT JOIN PartMaster AS P ON WOShortages.PartNumber = P.PartNumber) LEFT JOIN (SELECT L.PartNumber,
SUM(L.Quantity) AS NettableQuantity
FROM InventoryLots L
LEFT JOIN DepartmentCodes D ON L.DepartmentCode = D.DepartmentCode
WHERE D.NettableFlag<>0
GROUP BY L.PartNumber ) AS L ON WOShortages.PartNumber = L.PartNumber
ORDER BY WOShortages.WONumber, WOShortages.PartNumber, WOShortages.UOM;
SECOND QUERY:
SELECT POD.PartNumber, Sum(([POD]![QuantityReceived]-[POD]![QuantityOrdered]*-1)) AS QtyRemaining
FROM POHeader AS POH INNER JOIN PODetail AS POD ON POH.PONumber = POD.PONumber
GROUP BY POD.PartNumber, POH.ClosedFlag
HAVING (((Sum(([POD]![QuantityReceived]-[POD]![QuantityOrdered]*-1)))>0) AND ((POH.ClosedFlag)=0));