CODEWITHSUNDEEP
ms-accessbuilder
PapaDaniel
PapaDaniel

Reputation:

Microsoft Access - grand total adding multiple fields together

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 those one out. Just get a blank so I am sure there is an error.

Upvotes: 1

Views: 33007

Answers (3)

Gordon Bell
Gordon Bell

Reputation: 13633

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))

Upvotes: 5

Shane Miskin
Shane Miskin

Reputation: 1911

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

Upvotes: 1

Stephen Wrighton
Stephen Wrighton

Reputation: 37819

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;

Upvotes: 1

Related Questions