Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Problem (2002 (XP))

    I have a query that uses 2 tables. It uses one of the fields to create a sum. If there is no information to sum it excludes the whole record. I want it to return a value of 0 and use it in a calculation.

  2. #2
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Query Problem (2002 (XP))

    Think I understand. Try looking at the NZ() function.

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (2002 (XP))

    That looks like it. I can't seem to make it work. I'm using a total in the query to sum the field. I'm trying to put the NZ in the criteria but I don't know what to put in the expression. I have the Part of the DB I'm working if you could look at it. When you run the query The Access Class needs to show with a Tuition Sum of 0. For Combo1 enter Fall and Combo3 Enter 2002 You can download a stripped version of the DB with table form query and report ftom

    www.comsectech.com/qry.mdb

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query Problem (2002 (XP))

    Use the NZ funtion in an expression in your query, and sum the expression rather than putting it in the criteria.
    Wendell

  5. #5
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (2002 (XP))

    Deleted the summing curTuition Field and replaced it with Tuition: Nz([curTuition],0)
    It still doesn't include the class without a registration. I think I need something that says if no registration found let Tuition = 0

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Query Problem (2002 (XP))

    The Nz function works as advertised, your problem was that the query used an inner join, you need to have an outer join to include the class that has no registrations.

    The revised query SQL looks like this:

    SELECT tblClass.strSemester, tblClass.intYear, tblClass.strClassName, tblClass.dtmStartDate, tblClass.curStipend, Sum(Nz([curTuition],0)) AS Tuition
    FROM tblClass LEFT JOIN tblRegister ON tblClass.lngClassNumber = tblRegister.lngClassNumber
    GROUP BY tblClass.strSemester, tblClass.intYear, tblClass.strClassName, tblClass.dtmStartDate, tblClass.curStipend
    HAVING (((tblClass.strSemester)=[Forms]![frmReportsSemester]![Combo1]) AND ((tblClass.intYear)=[Forms]![frmReportsSemester]![Combo3]));

    I highlighted Nz expression & JOIN clause in bold. Also have attached revised copy of your sample db so you can see how it works.

    PS - no one signed up for the Access class?? (joke)

    HTH
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (2002 (XP))

    Again Mark You are the greatest! I've been beating myself to death over this one. I have a long way to go but I'm getting there. Thanks So Very much

Posting Permissions

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