Need Help

[Loony22]

As u see in the queries below, when running the 2nd query the result will be: PatientId, LastName, FirstName, TotalSumPayed and
Debt: (TblHostelInfo].EconomyPayment-QryEconomyPaymentDebt.TotalSumPayed).

I would like to "upgrade" the query. If the patient didn't pay any kind of fee to EconomyPayment, the query will show that he has 0 TotalSumPayed in the specific month. However for example, if the patient's "DateOfEnterance" is 28/06/2002, he won't have debts for the 5/2002 and less.Of course, the patient won't have debts for the 09/2002 (its in the future ).
Just for the record, Payments Takes place each month.
Moreover, the current query shows only the one's who paid and not the one's who didn't.


In my current DB there are the following Tables and Queries

TblPatient :

PatientId
LastName
FirstName
HostelId
DateOfEnterance (the date the patient entered)

TblEconomyPayment :

EconomyPaymentId
PatientId
ReferenceId
PamentDate
PaymentMethod
SumPayed
PaymentForMonth (for what month within the year did the patient payed)
PaymentForYear (for what year did the patient payed)


TblHostelInfo :

EconomyPayment ( the price needed to pay each month)

QryEconomyPaymentDebt1 :
SELECT TblEconomyPayment.PaymentForYear, TblPatient.FirstName, TblEconomyPayment.PaymentForMonth, TblPatient.LastName, TblPatient.PatientId, TblPatient.CurrentStatus, Sum(TblEconomyPayment.SumPayed) AS TotalSumPayed
FROM TblPatient INNER JOIN TblEconomyPayment ON TblPatient.PatientId = TblEconomyPayment.PatientId
GROUP BY TblEconomyPayment.PaymentForYear, TblPatient.FirstName, TblEconomyPayment.PaymentForMonth, TblPatient.LastName, TblPatient.PatientId, TblPatient.CurrentStatus;

QryEconomyPaymentDebt2:
SELECT [TblHostelInfo].EconomyPayment-QryEconomyPaymentDebt.TotalSumPayed AS Debt, QryEconomyPaymentDebt.PaymentForYear, QryEconomyPaymentDebt.FirstName, QryEconomyPaymentDebt.PaymentForMonth, QryEconomyPaymentDebt.LastName, QryEconomyPaymentDebt.CurrentStatus, QryEconomyPaymentDebt.PatientId, QryEconomyPaymentDebt.TotalSumPayed
FROM QryEconomyPaymentDebt, TblHostelInfo ;

 

[John.Woody]

Create the following function in a module

Function NullToZero(anyvalue As Variant) As Variant
' Accepts: a variant value
' Purpose: converts null values to zeros
' Returns: a zero or non-null value
' From: User's Guide Chapter 17

If IsNull(anyvalue) Then
NullToZero = 0
Else
NullToZero = anyvalue
End If

End Function


Debt: NullToZero((TblHostelInfo].EconomyPayment)-QryEconomyPaymentDebt.TotalSumPayed).

HTH
John

 

[Loony22]

Thank you for your help. However, that is not what i am looking for.
Maybe i'll give an example:

TbleconomyPayment :

MyPatientId = 123456
MyPatientDateOfEnterance = 28/05/2002

TblEconomyPayment :

EconomyPaymentId = 1
PatientId = 123456
ReferenceId = 0
PamentDate = 28/06/2002
SumPayed = 200 $
PaymentForMonth = 6
PaymentForYear = 2002


In TbleconomyPayment MyPatient is registerd as if he payed 200$ in the Month of 06/2002. But, in the month of 08/2002 and 07/2002 he is not registerd at all. How do i make that the Patient has a debt in these months? Second, i want that the computer won't "say" that MyPatient has debts for : 05/2002, 04/2002, 03/2002 and etc (its before he got into the DB) AND that he has not Debts in 09/2002, 10/2002 and Etc (In the future) .

 

[Rich]

You need to do some reading on Database design, you already have the Date paid, you do not need separate fields for month and year , a simple Sum due - Sum paid will show the balance at any time in a totals query

 

[Loony22]

I disagree totaly. Lets asume i pay in the 27/7/2002 but i pay for month 06/2002 . Its different, isn't it?

 

[glynch]

I agree with Rich. I have worked on systems where there have been attempts to try to track payments to specific billing periods. It is not only a nightmare to code, but business rules like that aren't followed properly by the users, or can't be properly followed,and as soon as you start getting inaccurate data in there it all becomes a mess.

For example, suppose you have a client who has a monthly bill of $200. They make a partial payment for May 2002 of $75 (leaving a balance of $125). They make no payments for three months, then you get a check in Sept 2002 for $585. To force the data entry person to make multiple payment entries ($125 for May, $200 for June, $200 for July, $60 for August, leaving an oustanding balance for August of $140), mistakes will happen so often that it is not worth it, believe me.

What is really important is tha actual balance due, increment that on a monthly basis, apply all payments against the running balance, and everyone is happy.

That's my two cents, you can do what you want with it.

Good luck!

 

[Loony22]

Thank you very much for the second advice. It really opened my eyes for the mistakes that can happen. However, i didn't understand how my "TblEconomyPayment" should look. Moreover, how will the Query look so the Managar knows who paid and for what month ?

I hope you will help.

Ryan

 

[glynch]

If money is owed, isn't it much more important to know the total due rather than how much is due for specific months? If I owe you $585, that is all you really need to be interested in. You can have a monthly process that increments the amount due by the charges for each month, but I would only figure payments against the running outstanding balance.

I would have one table with info specific to each client, only one record per client. A billing table with one record per client per billing period (monthly?), and a payment table with one record per client for each time that client makes a payment.

To get the outstanding payment due at any time, just query the billing and payment tables and calculate the difference of the sum from each table.

In my previous example, if the client started in May 2002, by Sept 2002 they would have accumulated a sum of $1000 from the billing table, and a sum of $660 from the payments table, leaving an oustanding balance due of $340.

 

[Rich]

Unfortunately life is not always that simple, if Patients have a valid reason for withholding payments for a particular treatment or they can make several payments for one treatment then you have to store the treatment PK as the FK in a table as a one to many relationship with treatments, then you have the problems glynch pointed out. You need to follow the same principal if you want to track specific payments against specific treatments.

 

[The_Doc_Man]

I have had experience with the patient end of a billing system that tracks items by specific months or specific invoices. As you have already seen in posts from other forumites, there are many issues involved in trying to track this kind of payment system.

Basically, the only reasonable way to do this is to somehow capture an invoice number (bill number, statement number, your pick as to what you call it) with a payment. This of course implies that you also track the numbers in your accounts receivable list.

Worse, what do you do if a person has bills outstanding from both January and February and they give you a single check that covers both months? How will you track that?

I'm not trying to give you a hard time, I'm going somewhere with this.

Access is a modeling tool. It models your business actions and rules. So in order to understand what to make Access do, you must first "simulate" the same exact action with a manual data analysis pad or something else much like it. If you cannot do this, Access will be of limited use to you.

Your business consists of entities and the rules that define how those entities come into being, are modified, and disappear. The entities can be persons, payments, bills, accounts, medical visits, medical supply line-items, pharmaceuticals administered, etc. You must isolate those items that you want Access to track.

Do you only get the final bill, not any of its details? That tells you one thing about what you want to track. Do you see the details? That might entail a different type of report.

You must find out what it is that you really track for bills. Obviously, some kind of patient ID - but perhaps not so obvious if you REALLY track invoice and can only get the patient ID by joining to the Invoice table where that ID resides.

You must find out what it is that you really track for payments. Obviously again, some kind of patient ID. But now you have to know the rules for applying that payment to money owed. Particularly if the patient covers more than one invoice in a single payment.

Do you have patients who are paying on a fixed-amount-per-month situation? What are the rules in that case?

Do you have the situation where you cannot legally bill the patient until all government entitlements and private insurance bills have been paid?

You need to draw out a data flow model that governs bills and payments. You need this before you can hope to make Access do by machine what you wanted to be done on paper.

There is no substitute for detailed dataflow analysis when you have a complex problem in front of you. And, my friend, trust me. You have a complex problem in front of you.

 

[Loony22]

Thank you very much for your lovely post. It's outstanding how much help people are willing to give on the internet. .

Lest go to the point.

I did some checking with the CEO of the company about the MANUAL operation of the Patient Payments.
First, on each year or period of time the CEO get a report about Patients payment. In the report, there are as followed:

Patient ID
Payment for Month and Year (what month and year (
Sum Paid
Comments

He also mentioned that it's essential that the Payment for Month and Year will be in the Report. Also, there are possibilities that patient didn't pay for a particular month because he was away during that month. Thus, in comments filed, there is a mentioning that he was away. Moreover, there are possibilities that patient will pay for couple of months. However, he must imply for what month he is willing to pay.
Like The_Doc_Man mentioned, the invoice will be for the amount of money paid but the debt will be deleted for payment months.


Therefore, I decided to split the billing from payments:

TblBilling :

BillId
PatientId
BillKind ( rent, economy and etc etc) connected to TblBillingTypes
PaymentForMonth
PaymentForYear
PaymentSum (Sum needed to pay)

TblPayment :

PaymentId (Will be recorded for incvoice)
BillingId
PaymentMethod
PaymentDate
PaymentSum (Sum Paid)

However, I don't know how to fix one payment for multiply BillingId. I have the problem in font of me but I don't know how to solve it. If any of you can help me with the problem I will be glad.

 

[The_Doc_Man]

Don't limit yourself to the CEO (though that was an excellent starting point.) Ask some of the workers "in the trenches." As the accountants. In order to make this work, you need to undergo what my (government) office calls a "rules discovery."

We do military reservist pay processing here. When one of our systems was recently upgraded to an ORACLE DB, over 3300 rules were "discovered" that applied to such things as enlistment, re-enlistment, transfer between reserve units, promotions, extra duty, training credits, mobilization, etc. etc. etc. I hope you have about two orders of magnitude fewer rules.

 

[Loony22]

I have asked the workers about the way of treatment in patient’s debt. Here is the main part of their answer.

There are few ways of payment: Cash, Check, Permanent Order on each month (directly to the bank account) and credit card.

If the patient paid in check the worker is entering these Fields (all manually):

Patient ID
PaymentFor (Rent, Economy and etc )
Check Number
Bank Number (there are special numbers for each bank)
Bank Account Number
Sum Paid
Payment For Months, Years (patient can pay for few months. i.e 08/2002, 10/2002 and etc)


If patient pays with Permanent Order each month the worker is entering these fields:

Patient ID
PaymentFor (Rent, Economy and etc )
Reference Number
Sum Paid
Payment For Months, Years (patient can pay for few months. i.e 08/2002, 10/2002 and etc)

If patient pays in cash each month the worker is entering these fields:

Patient ID
PaymentFor (Rent, Economy and etc )
Sum Paid
Payment For Months, Years (patient can pay for few months. i.e 08/2002, 10/2002 and etc)

Each worker knows exactly for what month the payment goes to. If the payment is larger then the sum needed for the month they associate it for the next month owed. If the patient doesn’t need to pay in the next month, they associate it for the month after. The same system is used in case when payments are given is advance (for future debts). However, there are cases when patients are paying only part of the sum needed for the month. Finally, in the end of the year, they output a report of all patients payment for the specific treatment (as I mentioned in earlier post).

I thought of good solutions two of my problems:
1) Each payment method will have its own table.
2) for changing rates: On Each Payment the worker will be asked to enter the payment rate. The program will divide the rate by months entered.

My problem is the payment report. How will I make a report similar to the file I have attached (MS excel).

 

[The_Doc_Man]

There might be problems with having separate tables for separate payment types.

If all of this is done via forms, then you can put validation rules into the forms rather than the tables. Perhaps you can define a single table that holds all required fields for the aggregate of all payment types, but have a code field that shows which payment type. Then, you could write code that only looks at fields consistent with that payment type.

Another thought - a single payment table with links to separate payment detail tables. In other words, everybody makes payments. Put the common stuff in that table. Plus a "method" field. Then make separate tables for the unique portions of each method, linked back to the main payment table.

A third thought - use a UNION query on your separate payment tables to build a single recordsource holding all common elements from all the component tables.

The reason I'm suggesting single recordsource methods is that when you do your accounting, you STILL really don't care how the money got there when you apply money against an account. At least, that is what your rules seem to say.

You either pay a month in full or you don't. You either have extra money to apply to subsequent debts or you don't. You can change your payment type in a year, or not.

Having to apply this same test several times to determine an account balance will lead to terrible confusion. You will make your problem harder, not easier.

Due to security requirements at this site (U.S. military) I cannot download your file. I will have to leave your "how can I make this report" question to others who can do the download.

 

[Loony22]

Thank you very much indeed. This has been very helpful for me.
Any of u who can help me make this report please reply.