Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2013
    Posts
    9

    Unanswered: Returning Zero if no data in second record set

    Hello,

    I am looking to design a report which compares the current year's records with records from the prior year. Is there a way to return a zero for the quantity if say my table data looks like below:

    Table A 2013 Table B 2012

    Variable QTY Variable QTY
    Y 2 Y 6
    x 3


    Id like the query to return:

    2012 y 6 2013 y 2
    2012 x 0 2013 x 3

    Right now when i compare data, I only see records that have value or zero keyed in. Is there anyway for access to enter zero if the record does not exist in a prior year?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This should work:
    Code:
    SELECT 2012 AS 2012, Table_B_2012.Variable, Nz(Table_B_2012.QTY,0), 
           2013 AS 2013, Table_A_2013.Variable, Nz(Table_A_2013.QTY,0)
      FROM Table_A_2013 LEFT JOIN Table_B_2012 
        ON Table_A_2013.Variable = Table_B_2012.Variable;
    Have a nice day!

  3. #3
    Join Date
    Apr 2013
    Posts
    9
    How would I build that function into a query? Or would those statements go into the text box on the report level?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That IS a query, not a function!
    Have a nice day!

Posting Permissions

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