Summing two fields when one is blank

[Guest]

Hi,

I have three tables. One is called TherapistMain and has two fields:
TherapistName WeeklyTargetRevenue. I then have a WeeklyProductRevenue and a
WeeklyServiceRevenueTable. Both of these tables have the two fields which
include TherapistName and Revenue. There are frequent situtations where the
therapist will have only product revenue or only service revenue for a given
week. Most of the time they have both. However, the therapist will not
appear in the WeeklyProductRevenue table if they have no product revenue for
the week and they will not appear in the WeeklyServiceRevenue if they have
not service revenue for the week. I have created a query that links the
three together with outer joins. I selected TherapistName from the
TherapistMain so that it always appears and then have ProductRevenue and
ServiceRevenue. I then created a calculated field called Total which sums
the product and service revenue.

The problem is that if a therapist has not product revenue and does have
service revenue, or has product revenue but no service revenue, the total is
zero. In other words, a value for total will not appear unless there is both
product and service revenue. What I would like is for a value of zero to
appear in the blanks with a format of currency and then have the total appear
even if they have not product revenue for the week or service revenue for the
week. Can someone help?

Thanks,


Chuck W

 

[Skip]

Hi,

not service revenue for the week. I have created a query that links the
three together with outer joins. I selected TherapistName from the
TherapistMain so that it always appears and then have ProductRevenue and
ServiceRevenue. I then created a calculated field called Total which sums
the product and service revenue.

Would the Nz() function work? I.e. Total:Nz([Product],0)+Nz([Service],0)
If [Product] is not null, it uses the value of [Product], otherwise it
substitutes 0 for the Null.

Hope I got the syntax correct there.

 

[Guest]

Use the NZ (Null to zero) function. If you do each field as an NZ then if
it's null a zero will be put into that field.

 

[Steve Schapel]

Chuck,

Skip got it right as regards your specific question.

However, allow me to make another comment... The normal way to
construct this database would be to put all your Revenue in one table,
and not split it into the Product and Service tables, which is known as
the "tables as data" trap. Instead, you should have a field in the
Revenue table that identifies each record as being either Product or
Service. If you had it set up like that, the problem you asked about
would not exist, and also a lot of other things would turn out to be
simpler and quicker as well.