allocated plus on order exceeds onhand

[rwn]

I have this sql query and when it runs, most of the data is correct. But then on some pars the allocated is extremely off (higher than it should be). The formula is below.
SELECT material.Material, material.Description, Material.On_Order_Qty, iif( not isnull(mSum.AQty), msum.aqty,0) AS Alloc_Qty, iif( Not Isnull(OH.On_Hand),Oh.on_hand,0) AS OH_QTY
FROM (material LEFT JOIN [SELECT Mdetail.Material, Sum(Mdetail.Alloc) AS AQty FROM (Select Material_req.Material, Sum(Material_Req.Deferred_Qty) as Alloc From Material_Req Group by Material_Req.Material Union All Select SO_Detail.Material, Sum(SO_detail.Deferred_Qty) as Alloc from SO_Detail group by SO_Detail.Material) AS Mdetail GROUP BY Mdetail.Material]. AS mSum ON material.Material = mSum.Material) LEFT JOIN [Select Material_Location.Material, Sum(Material_Location.On_Hand_Qty) as On_Hand From Material_Location Group by Material_Location.material]. AS OH ON material.Material = OH.Material
WHERE iif( not isnull(mSum.AQty), msum.aqty,0) > iif( Not Isnull(OH.On_Hand),Oh.on_hand,0);


The formula that is just to view the Allocated is below and always is correct. Is there a way to use this formula below in the formula above or at least make the formula above be accurate? Thank you!!

SELECT q_Allocated.Material, Sum(q_Allocated.Deferred_Qty) AS Allocated
FROM [Select * from q_Allocated_Via_Jobs Union all Select * from q_Allocated_Via_So]. AS q_Allocated
GROUP BY q_Allocated.Material;