How Can I Use a SubQuery to Sum a Column "Quantity" where subqry.item=mainqry.it

More to the point, how do I get the results to show one row per Item? I thought if I got all the rows evaluate the same, I would be able to use DISTINCT to return one row per item.

Actually, I thought if I did a RIGHT JOIN that for each instance (record) of an item in my ItemInventory table (in which all rows are unique) with my PurchaseOrderLine table (in which there can be multiple instances of an item) that I would get one lie, with the quantities on PO's that are open and received summed. Instead, I get multiple lines that all appear to have correct data but that are duplicates.

Here is my current query

SELECT ItemInventory.Name AS [Product Number],
ItemInventory.SalesDesc AS Description,
ItemInventory.PrefVendorRefFullName AS Vendor,
ItemInventory.QuantityOnHand AS Available,
ItemInventory.ReorderPoint AS Minimum,
(ItemInventory.QuantityOnHand-ItemInventory.ReorderPoint) AS Under,

(SELECT SUM(PurchaseOrderLine.PurchaseOrderLineQuantity)
FROM PurchaseOrderLine
WHERE ItemInventory.Name =
PurchaseOrderLine.PurchaseOrderLineItemRefFullName )
AS [On Order],

(SELECT SUM
(PurchaseOrderLine.PurchaseOrderLineReceivedQuanti ty)
FROM PurchaseOrderLine
WHERE ItemInventory.Name =
PurchaseOrderLine.PurchaseOrderLineItemRefFullName )
AS Recvd

PurchaseOrderLine.TxnDate AS [PO Date],
ItemInventory.PurchaseCost AS Price

FROM PurchaseOrderLine RIGHT JOIN ItemInventory ON PurchaseOrderLine.PurchaseOrderLineItemRefListID=I temInventory.ListID;

Here are my results:

"Large Box","Large Packing Box",,455,100,355,,,2
"Medium Box","Medium cardboard packing box",,55,100,-45,,,1.5
"Packing Tape","Packing Tape, 100 Yard roll",,100,100,0,150,100,1.5
"Packing Tape","Packing Tape, 100 Yard roll",,100,100,0,150,100,1.5
"Small Box","Small cardboard packing box",,55,100,-45,,,1
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Will Excel Delete This Item",,,100,,,,,0

If I use DISTINCT, like I thought I could, MS ACCESS blows up on me. I don't know if it is my SQL or MS ACCESS (or if my data source has issues).

If I can verify my SQL is valid, then I'll know if I need to look elsewhere for a resolution to my problem.

TIA

Danielyou need to develop a query like this in two steps

the first step is to ensure that the join is working correctly, and the second step is to add GROUP BY and aggregate functions like SUM (i think your subqueries in the SELECT clause are wrong, but let's do that later after we fix the join)

for your first step, you need to understand that your RIGHT JOIN will return each ItemInventory with or without matching rows from PurchaseOrderLine

in other words, all products

and for any product that appears on any PurchaseOrderLine, you will get a row for each such occurrence, and that product will appear in the results multiple times

is that what you want? if not, we need to fix the join before you start thinking about SUMs|||I need to look at every item in the InventoryItem table.

For those items where there are purchase orders in the PurchaseOrderLine table, I need to sum the total number on all open purchase orders, minus the number already received against those open purchase orders (they receive partial fills on orders).

In the end, I need somethig that looks like this:

Name Description Vendor #Available Reorder_Point Max_On_Hand #Under Reorder_Point #On_Order #To_Reorder Cost_Per_Unit Cost_to_Reorder Total_Cost_to_Reorder

#Under = Max_On_Hand - #Avaialble (if #Available < Max_On_Hand)

We reorder an item if #Under < 0

#To_Reorder = #Under - #On_Order (we never want the total headed for the shelves to be > Max_On_Hand|||select II.Name AS [Product Number]
, II.SalesDesc AS Description
, II.PrefVendorRefFullName AS Vendor
, II.QuantityOnHand AS Available
, II.ReorderPoint AS Minimum
, (II.QuantityOnHand
-II.ReorderPoint) AS Under
, SUM(POL.PurchaseOrderLineQuantity)
AS [On Order]
, SUM(POL.PurchaseOrderLineReceivedQuantity)
AS Recvd
, MAX(POL.TxnDate) AS [PO Date]
, II.PurchaseCost AS Price
from ItemInventory as II
left outer
join PurchaseOrderLine as POL
ON POL.PurchaseOrderLineItemRefListID
= II.ListID
group
by II.Name
, II.SalesDesc
, II.PrefVendorRefFullName
, II.QuantityOnHand
, II.ReorderPoint
, II.PurchaseCost|||I will try this.

I may ask what makes it tick, but I think I get it ... I will play with it before I ask again tho.