Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92

    Unanswered: Sorting and Sums

    In short, I'm tring to sort product information and sum their totals for each order placed, but I still want it to show if 0 were ordered on an item. This way I always have the same length/style forms for filling out and reading.

    I'm obviously missing some sort of SUM or SUMIF statement, but not quite sure how to go about it.

    Can somebody help, or give a good idea of how to proceed?

    Example:
    Product--Product ID-----BOM ID----Quantity
    Item A -----1-------------1----------42
    Item B -----1-------------1----------13
    Item C -----1-------------1-----------0
    Item D -----1-------------1-----------0
    Item E -----2-------------2----------12

    What I want it to if I sort it by ID 1 / BOM ID 1 is:
    Item A ---1---1--42
    Item B ---1---1--13
    Item C ---1---1--0
    Item D ---1---1--0

    What I currently get is:
    Item A ---1---1--42
    Item B ---1---1--13

    Heres my code so far:

    SELECT tblPartsList.strPartNum, tblPartsList.strStockNum, tblPartsList.strDescription, tblPartsList.Manufacturer, tblBOMdetails.QuantityRequested, tblInvCount.OnHand, tblBOMdetails.QuantityShipped, tblInvCount.OnOrder, tblInvCount.Remarks
    FROM tblPartsList INNER JOIN (tblInvCount INNER JOIN tblBOMdetails ON tblInvCount.ItemID = tblBOMdetails.ItemID) ON tblPartsList.ID = tblBOMdetails.ItemID
    WHERE (((tblPartsList.ItemCode)=1) AND ((tblBOMdetails.BomID)=[frmBOMmainSheet].[BOMID]));

    Please let me know if any more information will help.
    Thanx

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need a LEFT OUTER JOIN

    one question: where's the frmBOMmainSheet table?
    Code:
    select tblPartsList.strPartNum
         , tblPartsList.strStockNum
         , tblPartsList.strDescription
         , tblPartsList.Manufacturer
         , tblBOMdetails.QuantityRequested
         , iif(isnull(tblInvCount.OnHand),0,tblInvCount.OnHand) as OH
         , tblBOMdetails.QuantityShipped
         , iif(isnull(tblInvCount.OnOrder),0,tblInvCount.OnOrder) as OO
         , iif(isnull(tblInvCount.Remarks),'',tblInvCount.Remarks) as Rem
      from (
           tblPartsList 
    inner
      join tblBOMdetails 
        on tblPartsList.ID 
         = tblBOMdetails.ItemID
           )
    left outer 
      join tblInvCount 
        on tblBOMdetails.ItemID
         = tblInvCount.ItemID 
     where tblPartsList.ItemCode = 1 
       and tblBOMdetails.BomID = [frmBOMmainSheet].[BOMID]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    actually there isnt one. The frmBOMmainSheet is actually the form that brings all of the Bill of Material information together with the parts and the purchasing company Basically the form this search is on, contains all of the purchasing companies information. I'm using this search to filter the information on all of the purchases they have made at a specific time.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, let me know if the query works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Not yet, tried copying it in, but now only have a partial list, and have #Name? in the On Hand, On Order, and Remarks fields.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hmmm... isn't tblInvCount the table that has the missing rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    no...the problem is that if the Requested Quantity is 0, then the row doesnt show up at all in the query.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you mean that there is no row in tblBOMdetails?

    because that would mean i've got the LEFT OUTER JOIN to the wrong table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    ok, i think i just figured out a major portion of my problem. I have just realized that if an item does not have anyhting requested, it does not receive a BOM ID code assigning it to the BOM I'm searching.

  10. #10
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    yes, I do have rows in tblBOMdetails....

    RowID/BomID/ItemID/QuantityRequested/QuantityShipped

    all filled in as well.

  11. #11
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Previous Example Corrected:
    Product--Product ID-----BOM ID----Quantity
    Item A -----1-------------1----------42
    Item B -----1-------------1----------13
    Item C -----1-------------------------0
    Item D -----1-------------------------0
    Item E -----2-------------2----------12

  12. #12
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    r937,
    Question for you. If I build a new form for inputting new BOMs, and enter in all of the items requested, as well as the ones not requested, would this fix the issue?
    I think this would assign a BomID to all of the Items, and possibly allow the search method to pull up all of the information, including the ones with 0 items requested.
    I know it might make a larger table in the database, but I can work on moving old files out of it later if it becomes an issue.

  13. #13
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    looks like you're offline now...thanks for the time, and help anyways, as I believe it's led me to the right solution.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by inzzane
    If I build a new form for inputting new BOMs, and enter in all of the items requested, as well as the ones not requested, would this fix the issue?
    it might, but i wouldn't bother

    do some research on LEFT OUTER JOIN and you'll see it's exactly what you want for rows that are missing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Thanks, I'll read up on that.

Posting Permissions

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