Microsoft Access - grand total adding multiple fields together

Question

I can't quite figure this out. Microsoft Access 2000, on the report total section I have totals for three columns that are just numbers. These =Sum[(ThisColumn1)], 2, 3, etc and those grand totls all work fine.

I want to have another column that says =Sum([ThisColumn1])+Sum([ThisColumn2]) + Sum([ThisColumn3]) but can't figure thos one out. Just get a blank so I am sure there is an error.

Answer 1

Give the 3 Grand Totals meaningful Control Names and then for the Grand Grand Total use:

=[GrandTotal1] + [GrandTotal2] + [GrandTotal3]

Your Grand Total formulas should be something like:

=Sum(Nz([ThisColumn1], 0))

Answer 2

Create a new query, and the sql should look like this:

SELECT SUM(Column1 + Column2 + Column3),
       SUM(Column1),
       SUM(Column2),
       SUM(Column3),
  FROM Your_Table;

Answer 3

NULL values propagate through an expression which means that if any of your three subtotals are blank, the final total will also be blank. For example:

NULL + 10 = NULL

Access has a built in function that you can use to convert NULL values to zero.

NZ( FieldName, ValueIfNull )

You can use NZ in reports, queries, forms and VBA.

So the example above could read like this:

=NZ([GrandTotal1],0) + NZ([GrandTotal2],0) + NZ([GrandTotal3],0)

http://office.microsoft.com/en-us/access/HA012288901033.aspx