HI I am trying to do a inventory database and need 3 numbers to calc in a qry. I have a table for the start qty/prod info, one for Work ords and one for receipts/corrections. The prob is not all the items (4256 total) have data in all the qrys. I need the null fields "0" so that the item shows up on the end Inventory total qry (step3) My SQL for the 3rd qry is :
SELECT [Inventory Record].Part_Number, [Inventory Record].Qty_in_Stock, [QrySumRcvd - A].[SumOfQty (+ / - )], [QrySumIssued - B].SumOfQty_Issued, [Qty_in_stock]-[SumofQty_issued]+[QrySumRcvd - A]![SumOfQty (+ / - )] AS [ONHAND Qty]
FROM ([Inventory Record] LEFT JOIN [QrySumIssued - B] ON [Inventory Record].Part_Number = [QrySumIssued - B].Part_Number) LEFT JOIN [QrySumRcvd - A] ON [Inventory Record].Part_Number = [QrySumRcvd - A].Part_Number;
This is in the final qry to build the report from, a basic report but I can't get the 0 instead of the Null spaces. Which qry do I add the changes to, to get the 0's in place of nulls??? I appreciate any help, thanks!
SELECT [Inventory Record].Part_Number, [Inventory Record].Qty_in_Stock, [QrySumRcvd - A].[SumOfQty (+ / - )], [QrySumIssued - B].SumOfQty_Issued, [Qty_in_stock]-[SumofQty_issued]+[QrySumRcvd - A]![SumOfQty (+ / - )] AS [ONHAND Qty]
FROM ([Inventory Record] LEFT JOIN [QrySumIssued - B] ON [Inventory Record].Part_Number = [QrySumIssued - B].Part_Number) LEFT JOIN [QrySumRcvd - A] ON [Inventory Record].Part_Number = [QrySumRcvd - A].Part_Number;
This is in the final qry to build the report from, a basic report but I can't get the 0 instead of the Null spaces. Which qry do I add the changes to, to get the 0's in place of nulls??? I appreciate any help, thanks!