If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need Final Balance Formula for all Patients!
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 he 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 |
#2
|
|||
|
|||
Need Final Balance Formula for all Patients!
freemind wrote:
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. -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Need Final Balance Formula for all Patients!
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 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. -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Need Final Balance Formula for all Patients!
I figured out my problem--I needed an "nz" expression.
I appreciate the help.... Nancy |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Editing an existing formula | Jen | Worksheet Functions | 1 | October 24th, 2005 07:55 PM |
Hide formula | skateblade | Worksheet Functions | 10 | October 15th, 2005 08:36 PM |
Word 2003: track changes opens in final view instead of final sho. | JenniferLee | General Discussion | 2 | April 19th, 2005 06:25 PM |
Is there a formula for a decreasing montly balance of a mortgage? | jnorrisjr | Worksheet Functions | 2 | July 31st, 2004 05:43 AM |
Link to a formula. | AFB | Worksheet Functions | 2 | October 9th, 2003 03:42 PM |