Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    12

    Unanswered: handling #Error ?

    Imageshack - 53389983

    is the image i will be using as a reference in this thread


    Hello,

    I am designing a query and have encountered an issue that i dont know how to deal with.


    I've created 2 queries:


    Query 1: selects data found in several tables. This is a very specific query and its purpose is to find order numbers that have "doors" associated to them. (Our database contains manufacturing data, ie : order numbers, door quantity, etc). It is possible that an order number does not have a door quantity value associated with it. Here is a picture of part of the results:
    Imageshack - 15746779 I want you to notice that there is not a value for every single order number, ie: order numbers 2159 and 2160 are not found in the results, but they are order numbers that do exsist.. they just dont have a door number quantity associated with them.


    Because of the way Query 1 was designed (grouping and sum of the field quantity).. I believe that i am forced to create a 2nd query that adresses the order numbers that have no doors associated with them and put a value of 0.

    Query 2 : Imageshack - 53389983 This is the result of Query 2. Query 2 selects ALL the order numbers that exist in the database and has a left join on Query 1 based on ordOrderNo. Because there is not a qty value found for every ordOrderNo value, query 1 returns #Error.



    Desired output:

    Every order number should have a value for Qty. If that value is not found in Query 1 (meaning there are no doors on that order number) then it should have a value of 0.


    is there a way i can join the tables properly or do something about this?


    thanks


    feedback is always welcomed

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please can you post the SQL for both these queries?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2009
    Posts
    12

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Check for null values in your records:

    T-SQL
    COALESCE(dbo_OrderLineSAItems.olnsQuantity,0)

    Access
    nz(dbo_OrderLineSAItems.olnsQuantity,0)
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Sep 2009
    Posts
    12
    well i've solved my issue!


    here's what i did :


    Since Query1 found the order numbers that had a quantity value > 1, I built a query that would find every order number that is NOT present in the results of Query1. The code is the following:

    SELECT dbo_Orders.ordOrderNo, 0 AS qty
    FROM dbo_Orders LEFT JOIN Doors ON dbo_Orders.ordOrderNo=Doors.ordOrderNo
    WHERE (((Doors.ordOrderNo) Is Null));


    and as you can see, i'm hardcoding a value of 0 in a field i label "qty".

    So my results are :

    Query1:
    contains order number
    contains value of qty


    new query (query3)
    contains order number
    contains 0 as value for qty


    Next is real simple, i've created a 4th query that merges Query1 and Query3 and the code for that is:

    SELECT [Doors].*
    FROM [Doors]

    UNION ALL SELECT [orderno-no-doors].*
    FROM [orderno-no-doors];



  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Very glad this is solved...However sounds awfully complicated;

    This can almost certainly be done with a single query - Sorry I haven't got time to look right at the moment as I have to dash of but maybe someone else can have a look-see.

    Take it easy - if no-one else responds, I will try and post you more detail over the next few days.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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