Receipts/Credit Control

[Valedlobo]

Hi,

I am currently building a database which holds information on insurance policies. Each policy holder has the option of paying their annual policy either monthly, quarterly or annually.

At this stage I won't get too in depth, but I have built a table (TblReciepts) which the User enters each receipt as they arrive.

I would like to be able to match off each receipt against the outstanding balance on a realted policy, in this instance the policyholder should give their policyno as a reference in the receipt so that is where the relationship should be.

Can anyone suggest the most effective method of allocating a recipt to the outstanding balance in the table (TBLPolicy)

Regards

 

[edtab]

You mentioned that you have built a table called "TblReceipts".
I presume that you have at least two fields in this table as:

- PolicyID
- Amount

I am sure that you are aware that you need a form so the user
can enter data into your receipts table.

One possible way is to update your balance is to issue an SQL update statement as soon as the payment amount is entered (ie. do this in
the after update event).

I'll give you an example code:

Dim sql as string
sql = "UPDATE tblPolicy SET Balance = Balance - forms!TblReceipts!Amount WHERE PolicyID = forms!TBLReceipts!PolicyID"
Docmd.runsql sql

You'll have to change the above code accordingly where needed (e.g. I'm assuming that the balance is contained in a table called tblPolicy).

The other way is to create an update query using the query grid, save it, then run that update query after payment amount is entered, or on closing
of your payment form.

Let me know if this works for you.

 

[Rich]

You shouldn't store any balance, a simple query will tell you how much has been paid against specific invoices

 

[ScottGem]

I agree with Rich. You should NOT be storing a balance. As a general rule we do NOT store calculated values.

I assume you are storing the annualized premium for each policy somewhere. To calculate the balance, you add the total payments and subtract that from the annualized premium.

 

[neileg]

I agree with Scott and Rich ('cos they're right!).

However, you have some practical issues, here. User input can be notoriously unreliable. What happens if they quote a policy ID that is not valid? Or they try to pay more against the policy ID than is currently outstanding?

When dealing with this kind of balance question, I greatly prefer to hold the transactions all in one table, with the signs set so that a simple sum will give the balance on the policy, or by customer ID, or whatever. Thus I would be adding my policy premium amounts to a table and adding the payment records to the same table. A query that sums by policyID will give you the outstanding amount by policyID. Present the policy number(s) and the balance to the user in a form and ask them to allocat the payment to the policies. You can trap any allocation which violates the business rules. You will need to decide how to deal with overpayments.

It may be that it is not practical to combine the policy premium and the payments in the same table. In this case I would use a union query to produce the same effect.

 

[ScottGem]

Neil's points have merit, but I can see where it may not be practical to store the premium in the payments table. For example, the premium is fixed and annualized. So you would only want to store the premium once. I would agree in that circumstance you would use a Union query to get the balance.