best approach

[Fay Yocum]

I have the following columns 1. total billed, 2, PaymentAmount, 3 Remaining.
Remaining is a straight calculation of Total-PaymentAmount. Doing it this
way if there is no payment amount nothing shows in the remaining column. The
total billed comes from an invoiced table. The payments come from a payment
table. So obviously if there is no payment yet there is no entry in the
table for the invoiced billed.

I tried putting IIF(IsNull) in the PaymentAmount so that zero would show if
there was no payment, but the remaining column does not figure the remaining
value which should be the same as the total billed. How should I approach
this issue?

Thank you.

Fay

 

[John Vinson]

I have the following columns 1. total billed, 2, PaymentAmount, 3 Remaining.
Remaining is a straight calculation of Total-PaymentAmount.

In that case the field should not exist. Just calculate it on the fly
in a Query with an expression like

Remaining: NZ([TotalBilled]) - NZ([PaymentAmount])

in a vacant Field cell in a Query.

 

[Fay Yocum]

Thanks it worked like a charm. I will read up in NZ.

Fay

I have the following columns 1. total billed, 2, PaymentAmount, 3 Remaining.
Remaining is a straight calculation of Total-PaymentAmount.

In that case the field should not exist. Just calculate it on the fly
in a Query with an expression like

Remaining: NZ([TotalBilled]) - NZ([PaymentAmount])

in a vacant Field cell in a Query.

 

[HSalim]

1. set the default value of PaymentAmount to 0
2. Update YourTable Set PaymentAmount 0 where PaymentAmount Is Null
3. Update YourTable Set Remaining =[total Billed] - PaymentAmount



The best approach is to not approach it at all
Seriously, there are very few occasions when storing a calculated field pays
off.
One example of that is if you have a complex calculation that is best done
rarely, then it makes sense to store the value.
It is also bad database design because you now have doubled the work
Every time you get a payment, and write tio the payment table, you now have
to update this table as well

(the alternative, updating all payments before reading this table voids the
argument for having this table )

For plain vanilla stuff like this you could just build a query that will
return the calculated value.
For example:
Select [Total Billed], PaymentAmount, [Total Billed] - PaymentAmount as
Remaining
from yourtable