Need Final Balance Formula for all Patients!

[freemind]

Hello,

I am creating my first database for a physician who does one medical
procedure for one-time patients and then needs to bill the patients after
insurance pays.

I have created a table "PatientInformation" with the following fields:

ID
LastName
FirstName
(All Address info)
InsuranceID
ServiceDate
StartingBalance

I have created a table "Payments" with these fields:

PaymentID
PatientID
InsuranceID
PaymentDate
PaymentAmount
WriteOffAmount
InsuranceWithholdAmount
TotalDeduction (The sum of all "Amounts")

Payments can be made by the insurance or the patient. Each month, I need to
bill all patients with a balance. I am able to make a form that puts the
final balance by some convoluted queries showing the sum of all
"TotalDeductions" subtracted from the "StartingBalance".

The problem I'm having is getting invoices to show up for the newly entered
patients who have a starting balance, but no payments yet. Since they have
no "TotalDeductions", they don't show up.

I'd appreciate any insight as to how I can make this work! If the above
isn't quite making sense, you can view this database here:

http://www.sawyersweb.com/ForHelp.mdb
(This is my first attempt at a database, so if you see any other glaring
mistakes, feel free to point them out!)

Thanks!
Nancy

 

[Marshall Barton]

I am creating my first database for a physician who does one medical
procedure for one-time patients and then needs to bill the patients after
insurance pays.

I have created a table "PatientInformation" with the following fields:

ID
LastName
FirstName
(All Address info)
InsuranceID
ServiceDate
StartingBalance

I have created a table "Payments" with these fields:

PaymentID
PatientID
InsuranceID
PaymentDate
PaymentAmount
WriteOffAmount
InsuranceWithholdAmount
TotalDeduction (The sum of all "Amounts")

Payments can be made by the insurance or the patient. Each month, I need to
bill all patients with a balance. I am able to make a form that puts the
final balance by some convoluted queries showing the sum of all
"TotalDeductions" subtracted from the "StartingBalance".

The problem I'm having is getting invoices to show up for the newly entered
patients who have a starting balance, but no payments yet. Since they have
no "TotalDeductions", they don't show up.

The invoice report's record source query probably has an
Inner Join between the two tables when it needs to be an
outer join. Rght click on the line connecting the patient
table to the payments table and select Edit Relationship
from the little pop up menu. Then select the option with
Show all patient records and any matching payment records.

 

[freemind]

Okay, now I got them all to show up on the invoice report by editing the
joins (thanks!), but when they show up, the ones that didn't have payments
are just blank for the final balance instead of giving the amount due.

Nancy

 

[freemind]

I figured out my problem--I needed an "nz" expression.

I appreciate the help....

Nancy