[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1750
  • Last Modified:

Add Column Values to get a total in a Query

I have a Query with 5 insurance amounts ( Cancer, Accidental, Group, Short Term, and Sick).  I am wanting to create another column that adds all 5 of these and put the total in that column.  I know you can do it with the expression-
Total : [Cancer_Ammount]+[Accident_Ammount]+[ShortTermDis_Ammount]+[PersonalSick_Ammount]+[GroupTerm_Ammount]

The expression works only if all 5 columns have values.  Not everyone has all of these so some columns contain Null values.  I think that is were my problem is.  The expression will not add a Null value and thus will leave no information in the Total column it creates.
2 Solutions
Rey Obrero (Capricorn1)Commented:

Total : nz([Cancer_Ammount],0)+nz([Accident_Ammount],0)+nz([ShortTermDis_Ammount],0)+nz([PersonalSick_Ammount],0)+nz([GroupTerm_Ammount],0)
First method is to stop worrying about NULL's
- go to the design mode of the table
- set default value of each field to "0" (as all the fields are numeric fields, 0 will cause no problems)
By doing the above, your query will work without any problems. Because, if there is no value for particular field, then by default it will be taken as 0. So no questions of NULL.

Second method (just assisting the answer of capricorn1) use Nz function.
In Access, the Nz function lets you return a value when a variant is null.

The syntax for the Nz function is:  Nz ( variant, [ value_if_null ] )
variant is a variable that is a variant datatype.
value_if_null is optional. It is the value to use when the variant is a null value. If this parameter is omitted and the variant is a null value, the Nz function will return a zero or a zero-length string.

I tried the following by creating Table1 and making all fields numeric fields
SELECT Table1.a, Table1.b, Table1.c, Table1.d, Table1.e,
Nz(Table1.a,0) + Nz(Table1.b,0) + Nz(Table1.c,0) + Nz(Table1.d,0) + Nz(Table1.e,0) as Total
FROM Table1;

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now