how to calculate due dates of premium and update status for future

[rmch]

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

[Rabbit]

If you take the month number that you want to look at, you can use mod to determine if they have a payment due.

What you do is take the month number you want to look at, subtract the month number of their issue date, and then mod x. If it's equal to 0, then they have a payment due.

x is equal to
Monthly = 1
Quarterly = 3
Half Yearly = 6
Yearly = 12

As for the second thing you want, you will need to create a third table to log payments. Then it's a simple matter of creating a query with a where clause.

[rmch]

lot of thanks for replying, i think we are on track but i am new to access and can't understand what to do with all this practically, kindly write step by step procedure. i am thankful for your kind support

[Rabbit]

Giving someone the whole answer isn't my method of choice. Rather I point you in the right direction, you put in the work to understand and learn it, and you ask for clarification on specific points if you run into trouble while showing what you've attempted.

[rmch]

I am really excited about access and trying best to solve this, what i have done is....

I used this code in query…

SELECT tblContributionRegister.*,
IIf([Payment_Mode]="Monthly",DateAdd("m",1,[Issued_Date]),
IIf([Payment_Mode]="Quarterly",DateAdd("m",3,[Issued_Date]),
IIf([Payment_Mode]="Half Yearly",DateAdd("m",6,[Issued_Date]),
IIf([Payment_Mode]="Yearly",DateAdd("m",12,[Issued_Date]))))) AS Issued_Date_Plus, *
FROM tblContributionRegister;

Dates in Issued_Date_Plus are my desired dates. Now if a person pays premium on his due date how will I store this information for my later view. Also after payment this query should show me next due of certain person. eg. if personA has issued date 12-jan-2011 and mode is Quarterly, it means his next due will be 14-Apr-2011, so if he pays his due payment how would I update his status and save it for later view? Also how its possible that next time when I will run query the due date should be 14-July_2011.
Waiting your kind reply, kindly guide me how to create third table to log payments. i am not finding solution... plz

[Rabbit]

You create the third table the same way you created your other tables. You'll probably want the following fields:

PaymentID - Autonumber
 ContributionRegisterID - Whatever unique ID you are using for the Contribution Register table.
 DatePaymentMade - Date payment received
 DatePaymentFor - Date for payment period
 PaymentAmount
 

After that point, you have a few choices to track things. You could insert a new payment record after each payment is made that will have the date of the next payment. Or you could use the algorithm described earlier to determine whether someone has a payment due during the month you choose.

[rmch]

Thanks lot Rabbit. It is working, wowwww. I am really thankful for your kind support