A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need Final Balance Formula for all Patients!



 
 
Thread Tools Display Modes
  #1  
Old November 23rd, 2005, 05:56 PM posted to microsoft.public.access.reports
external usenet poster
 
Posts: n/a
Default 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  
Old November 23rd, 2005, 07:04 PM posted to microsoft.public.access.reports
external usenet poster
 
Posts: n/a
Default 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  
Old November 23rd, 2005, 09:33 PM posted to microsoft.public.access.reports
external usenet poster
 
Posts: n/a
Default 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  
Old November 23rd, 2005, 09:52 PM posted to microsoft.public.access.reports
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 06:19 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.