Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47

    Unanswered: How to make a Query return all records from Table 1...

    Sorry for the bland title, didn't know how to describe the problem.

    I have a mdb that has 2 tables. Table 1 list all the items and inventory levels, Table 2 is imported daily with units sold, however if an item isn't sold it is not listed in the table. I would like to create a query that shows updated inventory levels for all the items in Table 1.

    Below is an example:

    Table 1
    Item Inventory
    A 6
    B 5
    C 8
    D 3
    E 3

    Table 2
    Item Units_Sold
    A 3
    C 2
    D 1

    Query
    SELECT Table1.Item, Table1.Inventory, Table2.Units_Sold, ([Table1].[Inventory]-[Table2].[Units_Sold]) AS [Updated_Inventory]
    FROM Table1;

    Query Results
    Query1
    Item Inventory Units_Sold Updated_Inventory
    A 6 3 3
    C 8 2 6
    D 3 1 2

    Desired Results
    Item Inventory Units_Sold Updated_Inventory
    A 6 3 3
    B 5 0 5
    C 8 2 6
    D 3 1 2
    E 3 0 3


    Any ideas?

    From my research I think it has something to do with 'Empty' fields (not to be confused with 'Null', I've already tried that approach), but it might also have to do with my table joins.

    Thanks in advance!
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You've copied and pasted your query wrong - that query will error - it will not return any results.
    I think you have omitted an inner join, and that should instead be a left outer join. Post your code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    You are doubly correct, my code doesn't work, you are right omitted the inner join.
    The left outer join also solve the problem of displaying all the items, but there are now empty fields in units sold.

    I've searched for how to make an empty field = 0, most of the results discussed how to make null fields = 0. What I did find on empty fields wasn't easily understandable. Could you put how to do this laymen's terms?
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  4. #4
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    p.s. here is the code I have now
    Code:
    SELECT Table1.Item, Table1.Inventory, Table2.Units_Sold, ([Table1].[Inventory]-[Table2].[Units_Sold]) AS [Updated_Inventory]
    FROM Table1 LEFT JOIN Table2 ON Table1.Item = Table2.Item;
    If I add
    Code:
    WHERE (((Table2.Units_Sold)=Nz([Table2].[Units_Sold],"0")))
    the query only returns items on table 2 again.
    Last edited by Marsbars; 01-13-10 at 12:18.
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    NULLs are a somewhat controversial and slightly confusing concept. They do not belong in the pure relational model, however they are part of the ISO SQL definition upon which just about every relational database is based.

    You must be careful with your terminology. NULLs behave differently to other data, and introduce tri-value logic to database (True, False, NULL).

    Avoid using the term "empty". This is (very probably) NULL. If you mean NULL, use NULL. Note that a column value may appear "empty", but it could actually be a zero length string.

    Anyway, NULL indicates the absence of a value. It might be because there is no value or the value is unknown. The stuff you have found about converting NULLs to 0 should work fine. When you use left outer join, unmatched rows in the outer table will return NULLs for all column values.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    I apologize, I thought there was a difference between an 'empty' and 'Null'

    When I use the the left outer join the query returns all the items from table 1 like I want it to, the unmatched rows in the outer table return Nulls.

    However, when I use the NZ() function the query results omit the null items and only returns 3 records in table 2 instead of adhering to the left outer join and returning all 6 records from table 1 with 0 values instead of null.

    Code:
    SELECT Table1.Item, Table1.Inventory, Table2.Units_Sold, ([Table1].[Inventory]-[Table2].[Units_Sold]) AS Updated_Inventory
    FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Item = Table2.Item
    WHERE ((([Table2].Units_Sold)=NZ([Units_Sold],"0")));
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Marsbars View Post
    Code:
    WHERE ((([Table2].Units_Sold)=NZ([Units_Sold],"0")));
    you don't need this at all

    instead, put the Nz into the SELECT clause
    Code:
    SELECT Table1.Item
         , Table1.Inventory
         , Nz(Table2.Units_Sold,0) AS Units_Sold
         , Table1.Inventory - Nz(Table2.Units_Sold,0) AS Updated_Inventory
      FROM Table1 
    LEFT OUTER 
      JOIN Table2 
        ON Table2.Item = Table1.Item
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You don't want to put anything in the WHERE clause - you want all rows right? You just want to display 0 instead of NULL, correct? If so then you need to make the changes in the SELECT clause.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Like wot he did....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    I see, rookie mistake. Thanks so much guys, you rock! Was pulling my hair out all day yesterday because I couldn't find a solution in any of my books. Albeit, my library is rather small and incomplete.
    Last edited by Marsbars; 01-13-10 at 13:38. Reason: clarification
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

Posting Permissions

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