Recognizing non-record as zero

[shindb81]

I have a small clinic database with two tables, tblAppointments and tblPayments, which I would like to use for the query, qryBalance.


So one tblAppointments record can contain several tblPayments records, as patients can make several small payments for one visit. I set up the query to show the amount charged from tblAppointments and the sum of payments made for that particular appointment from tblPayments, and finally, the difference in a new column. So the query contains the following fields:

ApptID ApptDate AmountCharged SumofPaymtMade RemainingBalance

It works, but only when there is at least one tblPayments record existing for any given appointment. If there is no payment record, the appointment record doesn't even show, when I want it to return the amount charged as the balance.

I'm currently working around it by adding a payment record with 0 payment for all appointments without a payment, but I couldn't help but think there must be a better design.
Can I make Access recognize non-record as zero?

Thanks.

[June7]

Does the query use INNER JOIN? Use LEFT or RIGHT JOIN - "Include all records from tblAppointments and only those from tblPayments that match"

[ranman256]

You need an OUTER JOIN. (to show whats missing in 1 table)
in the Qry, dbl-click the line joining the 2 tables.
select 2, or 3 depending on what you want to see ALL of...(the apptmts)
save, run.

[shindb81]

Thank you to the above two!
That has actually solved that part of the problem.
The next step is to show only those appointments with a remaining balance. I made a new query, since it didn't seem like I could use a PaymtMadeOfSum in the same query as a variable for a calculated field.
So anyway, in the new query, I add a new field, "RemainingBalance: [AmountCharged]-[PaymtMadeOfSum]", which works fine, until I put a criteria of ">0".
At this time, I don't see the appointments with no payment made.

In fact, even in the first query, if I put the criteria of ">0" on the PaymtMadeOfSum, appointments without payments don't show because there is no record of payment.
How can I work around this?

[June7]

I expect the issue is because the appointments without payments have Null in the payments field in the query so the Sum of payments for those appointments is a Null.

Any arithmetic with Null results in Null. So handle possible Null.

RemainingBalance: [AmountCharged]-Nz([PaymtMadeOfSum],0)

[shindb81]

That did the trick!
Thank you