Total Field on an Access Query

[cantona]

I have a query which displays next to a persons name 3 costs. Id like to add another column to the query that totals these 3 columns. How do i write the code to take into account those people that dont have any costs.

For example.. My query looks like this..

Name Cost1 Cost2 Cost3 Total
Al 200
JB 100 150
QJ
CH 100 200 300

I would like to reurn a value of 0 if there arent any costs associated with a particular person.

 

[dhookom]

Do you understand that having 3 cost columns suggests your table is not normalized?

You can sum across fields with an expression like:
TotalCost: Nz(Cost1,0)+Nz(Cost2,0)+Nz(Cost3,0)

Also, [blue]Name[/blue] is not a good name for a field in Access since field has a name property.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]