I
Ian
I'm trying to work out a balance for each account in a
seriers of querys that are based on three tables.
Main tbl has the ClientID
Payment has the PaymentID linked to the ClientID
Property tbl links PropertyID to ClientID (1 to many)
Service tbl links ServiceID to PropertyID and each
service has a cost. Each cost is either rendered or not
rendered (Yes/No box)
Each service has a fee but the billing goes to the client
(who may have many properties each with many services).
I've created two queries one to sum the payments and the
other to sum the rendered costs
What I want is a (sum of payment)-(sum of rendered costs)
but if I create the expression
Balance: (NZ([sumofpayment],0))-(NZ([sumofcost],0))
All calculations with null values on one side or another
don't show at all. Can anyone help?
seriers of querys that are based on three tables.
Main tbl has the ClientID
Payment has the PaymentID linked to the ClientID
Property tbl links PropertyID to ClientID (1 to many)
Service tbl links ServiceID to PropertyID and each
service has a cost. Each cost is either rendered or not
rendered (Yes/No box)
Each service has a fee but the billing goes to the client
(who may have many properties each with many services).
I've created two queries one to sum the payments and the
other to sum the rendered costs
What I want is a (sum of payment)-(sum of rendered costs)
but if I create the expression
Balance: (NZ([sumofpayment],0))-(NZ([sumofcost],0))
All calculations with null values on one side or another
don't show at all. Can anyone help?