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

    Unanswered: Sort and Sum / Join Issue

    Using the following code for a bill of materials search, I get results returned where the ItemCode = 1, AND the Bill of Materials numbers match. This only produces the list of products that the chosen customers have purchased.

    What I really need it to do, is the same thing, but also list the other products that have an item code that =1 that were not purchased by the customer.

    I've done what I know how to do with the JOIN query, but just cant seem to get the full item list without getting a bunch of errors. Any ideas?

    'Code'
    select tblPartsList.strPartNum
    , tblPartsList.strStockNum
    , tblPartsList.strDescription
    , tblPartsList.Manufacturer
    , tblBOMdetails.QuantityRequested
    , tblInvCount.OnHand
    , tblBOMdetails.QuantityShipped
    , tblInvCount.OnOrder
    , tblInvCount.Remarks
    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]
    'End Code'

    Any help would be truly appreciated!

  2. #2
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    3 Tables:
    tblInvCount:
    rowID/ItemID/OnHand/MaxHold/ReOrderNum/OnOrder/Remarks
    tblBOMdetails:
    RowID/BomID/ItemID/QuantityRequested/QuantityShipped
    tblPartsList:
    ID/strPartNum/StrStockNum/strDescription/Manufacturer/ItemCode/TagsUsed


    What I am trying to do is get a full list of items from tblPartsList that has an ItemCode that equals 1. If the BomID matches the current form, then I would like the totals added for just that BOM.

    Currently, I get just the totals for that BOM, but not the full list from tblPartslist. I've been reading up on JOINS, but must be missing something. Does anybody know how to fix this?

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi
    Try this:
    Code:
    SELECT tblPartsList.strPartNum, tblPartsList.StrStockNum, tblPartsList.strDescription, tblPartsList.Manufacturer, tblBOMdetails.QuantityRequested, tblInvCount.OnHand, tblBOMdetails.QuantityShipped, tblInvCount.OnOrder, tblInvCount.Remarks
    FROM (tblPartsList LEFT JOIN tblBOMdetails ON tblPartsList.ID = tblBOMdetails.ItemID) LEFT JOIN tblInvCount ON tblBOMdetails.ItemID = tblInvCount.ItemID
    WHERE (tblPartsList.ItemCode="1") AND (tblBOMdetails.BomID="1" Or tblBOMdetails.BomID Is Null);
    Chris

  4. #4
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Hmm..I've never received this error before.

    Received the following error message with that code:

    This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

    It does appear to be typed correctly as far as all locations and tables go.

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I think I used strings as the number fields . Take the "" out of the two "1" expressions.

    If the above doesn't work, then you need to do two things to your original query:

    1. tblPartsList needs to be left inner joined to tblBOMdetails (a join with an arrow). That will ensure all the parts are included.
    In design view, double click on the join between these two tables and select the "Include all records from tblPartsList and only those records from tblBOMdetails where the join fields are equal."

    2. Include null joins.
    Because you have asked for records where BomID=1, then that's all you will get. But you want to include records where the right hand side of the join is null. So in design view, change the BomID criteria to:
    1 or Is Null

    HTH
    Chris

  6. #6
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    You were correct...it was the "" causing the problems....and it works perfect now! Thank you tons for the help on this, as it should make work quite a bit easier... now that I can pull up a historical record on the computer, instead of searching through paperwork!

    One last question though. I notice that when you enter code into this forum, it appears in it's own code box. How do you do 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
  •