# Thread: Cross Tab Totals (2003)

1. ## 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?

2. ## 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. ## 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?

Thanks again.

4. ## 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. ## 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. ## 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.

#### Posting Permissions

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