Insurance due dates and previous paid premiums details

[rmch77]

"Good Day..

Its my very first database project and I am really excited about it, trying to make for myself… I am an insurance manager and also learning access through free resources like training videos on youtube etc.. kindly help me on my problem

Two Tables, tblContributionRegister & tblAgentDetails.

tblContributionRegister has fields like.. Prospect_Name (text), Proposal_No (text), Policy_No (text), Submission_Date (Date/time), Issued_Date (date/time), Payment_Mode (combo box, list values are Monthly, Quarterly, Half Yearly, Yearly), Insurance_Term (number), Amount (number), Agent_ID (number)

tblAgentDetails has fields… Agent_Name (text), Agent_Code (text), Contact_No (text), Joining_Date (date/time)

I want……!

1- Lets say a personA is paying in quarterly mode and his issued_Date is 12-jan-2009. It means that his next payments will be 12-apr-2009, 12-jul-2009, 12-oct-2009 and so on. Another personB with Half Yearly mode of payment has Issued_Date 15-jan-2009 so his next payment dates would be 15-jul-2009, 15-jan-2010 and so on.

if I want to see on 28th-jun-2009 that who is due between 01-jul-2009 to 31-aug-2009 then results should be PersonA 12-jul-2009 and PersonB 15-jul-2009.

If I want to see on 28th-jun-2009, payment history of 1-jan-2009 to 28th-jun-2009 then results should be like this…..

PersonA 12-jan-2009 paid

PersonA 12-apr-2009 Pending

PersonB 15-jan-2009 paid

If personA pays today for his pending payment of 12-apr-2009 than I will update his status to paid.

Looking for step by step procedure to do this all. I am working in Ms Access 2007

Lot of thanks in advance

link to my db file: http://www.box.net/shared/o84spco1jxhdjquqgu1t

Software/Hardware used:
access 2007"

[timothybard]

Okay... not quick and simple and I don't have the specific details, but here is the general idea I would use.

I would write some VBA code that does the following:

1) Loop through each Policy
2) Withing each loop, keep track of a currentDate variable that is initially set to the Issue Date.
3) Increment the currentDate the appropriate number of months depending on the mode of the policy (monthly, quarterly, annual, etc.) until the specified day (in your example, 28-Jun-2009)
4) When incrementing currentDate, determine if a premium was paid for that day and record that fact.
5) To find the future premium dates, add the appropriate number of months to currentDate; that should give you the next premium due date.

The dateadd() function should be very helpful when adding a specific number of months to a date.

That probably doesn't give you all the detail you want, but again, you question isn't a small question either. Let me know if that helps at all or if you need more details, which will take a little longer to put together.

[timothybard]

Oh, by the way, I would recommend adding a table to track each payment (ID, tblContributionRegister ID, Date, Amount Paid)

[timothybard]

Below is a link to a zip file that performs some of what you were requesting as well as some comments regarding the file:

http://dl.dropbox.com/u/1904743/rmchDB%20Modified.accdb.zip

1) Since the database uses vba code, the first thing you will want to do when the database opens is enable macros.
2) Notice that a form appears with a button with the text "Find Status of Payments"
3) Before clicking on the button, open the tblContributionRegister table.
4) Notice that I added two policies; one quarterly policy with an issue date of 9/8/2010 and the other as a yearly policy with an issue date of 5/5/2005.
5) Now open the Premium Payment Status table. This table records the status of the premium payments. Notice that this table is empty. Close this table.
6) Now, go back to the form and click the button.
7) Some code will execute in the background, but, rather quickly, the Premium Payment Status table will open again.
8) Notice now that the table is populated with data; also notice that it shows all the Premium Payment Dates of all of the Policies and the status of each payment ("Paid", "Under Paid" or "Not Paid")
Therefore, as you can see, the database is now able to look at each policy and see the status of previous premium payments. However, there are some things the database does not catch, such as if a person pays the premium with two partial payments, pays on a day other than the premium due date, pays more than the required premium payment, etc. Additional, I did not do anything with future premium payments.

[rmch77]

Thanks lot timothybard, its really same i was wondering. also thanks for providing sample db... Have a nice day

[chuckc]

Hi, I have a similar application that works for me. It is slightly different than what was described by timothybard.

I have a payment table that contains (along with a few other things):
. Record ID (Auto number Primary key)
. Payment date
. Account number
. Payment $amount
. Payment due (Yes/No field)
. Payment due date

I use a query that selects rows (or records) where [Payment due] is true and a Payment due date that is less than a date which I enter. This can be sorted by [Payment due date]. For a new account I just put in the payment due information. When a payment is made, I enter a new payment record, with [Payment due] set to True, and I set the [Payment due] field in the previous payment record to false. In my case I use VB code to display the information and make changes to the table, but that is a frill. The query links to another table with account information.

Just a slightly different approach. Whatever works for you is best.

Regards,
chuckc