Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cross Tab Totals (2003)

    Hi

    I've created a Cross Tab Query that totals the Budget amounts based on a particular Fee Band.

    The CEO wants the totals for each Account Manager to be %s of these, thusly:

    If the Fee Band is 'Secured' it remains 100% of the Budgeted Amount
    If the Fee Band is 'Unsecured', the total should be 50% of the Budgeted Amount
    If the Fee Band is 'Targeted', the total should be 10% of the Budgeted Amount.

    This works fine until I then try to get a Grand Total for these figures.

    If I create an Expression that is =(Secured)+(Unsecured)+(Targeted), the Grand Total only works if each if these Fields contains a figure.

    For example

    John: (S)$5,000 (U)$1,000 (T)$1,000 TOTAL: $7,000
    Paul: (S)$5,000 (U) blank (T)$2,000 $TOTAL: BLANK

    Can anyone help and explain why this isn't working?

    Thanks in advance. [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cross Tab Totals (2003)

    You can use the Nz function to substitute 0 for null (blank) values:

    =Nz([Secured],0)+Nz([Unsecured],0)+Nz([Targeted],0)

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab Totals (2003)

    Thanks Hans

    I looked up NZ in the Help File, and that explained what it does. I wonder why Access works like that? 2+0 = 2 not 0. Is it just me, or is Access' logic flawed?

    Anyway, not your concern.

    I appreciate your help.

    Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cross Tab Totals (2003)

    Empty, or Null as the technical term is, is not the same as zero. Think of Null as a missing value: you don't know its value yet, it could be anything. So 2 + Null is unknown too, hence the result is Null.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab Totals (2003)

    Well, it's interesting to know the logic Access uses. Not sure it's the "right" logic for the real world, but that's just my humble opinion.

    Thanks for enlightening me! [img]/forums/images/smilies/smile.gif[/img]

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

    Re: Cross Tab Totals (2003)

    I think you will find that virtually all databases that support the concept of Null work that way. At least those I've worked with do it that way.
    Wendell

Posting Permissions

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