Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    15

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

    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

    Daniel

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2006
    Posts
    15

    Don't think the join needs to be fixed

    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2006
    Posts
    15

    Thanks!

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •