Query question

[Skip Bisconer]

I am confused as to how to get results I need from a select query.

I have three queries Inventory, Quantity on hand and Quantity on order query. What I am trying to get is the inventory part number and description along the the amount in inventory and the amount on order from a vendor. When I combine the Inventory query to the Quanty on hand I get all the part numbers up even the 0 balances.

I want this. But when I combine the the purchase order lines to see what is on order or back orderd I only inventory parts I get is those that have an amount on back order. If I can get it right this SQL should produce 4700 + records instead it only provides 500+ because it only retrieves those lines with inventory still on order.

SELECT Inventory.INPM_PART AS Part, Inventory.INPM_DESC AS Description, [IN Whse Quantities].INPM_BWID AS WhsLoc, [IN Whse Quantities].INPM_BQOH AS QOH, InventoryOnOrder.BalanceToCome, InventoryOnOrder.APVM_NAME, InventoryOnOrder.POOO_ORDR, InventoryOnOrder.POOrderDate, InventoryOnOrder.POOrderDate
FROM Inventory INNER JOIN (([IN Whse Quantities] INNER JOIN [IN Whse Qty Invoiced History] ON [IN Whse Quantities].INPM_PART = [IN Whse Qty Invoiced History].INWH_PART) INNER JOIN InventoryOnOrder ON [IN Whse Quantities].INPM_PART = InventoryOnOrder.POOO_PART) ON Inventory.INPM_PART = [IN Whse Quantities].INPM_PART
WHERE (((Inventory.INPM_PART) Not Like "-*" And (Inventory.INPM_PART) Not Like "/*") AND (([IN Whse Quantities].INPM_BWID)="1") AND ((InventoryOnOrder.POOrderDate) Like "*08"))
ORDER BY [IN Whse Quantities].INPM_PART;

 

[KenHigg]

Here, this is a little more readable and somebody may be more willing to help:

SELECT Inventory.INPM_PART AS Part, 
           Inventory.INPM_DESC AS Description, 
           [IN Whse Quantities].INPM_BWID AS WhsLoc, 
           [IN Whse Quantities].INPM_BQOH AS QOH, 
           InventoryOnOrder.BalanceToCome, 
           InventoryOnOrder.APVM_NAME, 
           InventoryOnOrder.POOO_ORDR, 
           InventoryOnOrder.POOrderDate, 
           InventoryOnOrder.POOrderDate
FROM Inventory 
INNER JOIN (([IN Whse Quantities] 
INNER JOIN [IN Whse Qty Invoiced History] 
           ON [IN Whse Quantities].INPM_PART = [IN Whse Qty Invoiced History].INWH_PART) 
INNER JOIN InventoryOnOrder 
           ON [IN Whse Quantities].INPM_PART = InventoryOnOrder.POOO_PART) 
           ON Inventory.INPM_PART = [IN Whse Quantities].INPM_PART
WHERE (((Inventory.INPM_PART) Not Like "-*" 
          And (Inventory.INPM_PART) Not Like "/*") 
          AND (([IN Whse Quantities].INPM_BWID)="1") 
          AND ((InventoryOnOrder.POOrderDate) Like "*08"))
ORDER BY [IN Whse Quantities].INPM_PART;