Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Question Unanswered: getting due dates and updating received premiums in insurance db

    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: rmchDB.zip - File Shared from Box.net - Free Online File Storage

    Software/Hardware used:
    access 2007"
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Hi rmch77,

    My employer won't allow me to download zip files or access online storage sites so I'm afraid this is all guess work.

    Create a Query and then switch it to the SQL view and copy and paste the various bits of code I show you here. You can always switch the view back to the Design View to get a better understanding of what the SQL is doing.

    It's not that difficult to do the first part of your post, but as you want a step by step...

    This code here will display everything in tblContributionRegister, with an additional column called Issued_Date_Plus. Issued_Date_Plus is Issued_Date + 3 months (created using the DateAdd(interval, number, date)

    The interval for DatedAdd can be "yyyy" for Years, "Q" for Quarter, "m" for month, "ww" for week, and "d" for day... there are more but these are the values I know.

    Code:
    SELECT DateAdd("m",3,[Issued_Date (date/time)]) AS Issued_Date_Plus, tblContributionRegister.*, *
    FROM tblContributionRegister;
    So the above will just add 3 months to everything. You need to add a number of if statements now so that if payment mode is "Monthly" then Add 1 month, if Quarterly add a Quarter, if Yearly add a Year...

    To make it easier, I left the interval as MONTH for all of these

    Code:
    SELECT tblContributionRegister.*,
    IIf([Payment_Mode]="Monthly",DateAdd("m",3,[Issued_Date (date/time)]),
    IIf([Payment_Mode]="Quarterly",DateAdd("m",4,[Issued_Date (date/time)]),
    IIf([Payment_Mode]="Half Yearly",DateAdd("m",6,[Issued_Date (date/time)]),
    IIf([Payment_Mode]="Yearly",DateAdd("m",12,[Issued_Date (date/time)]))))) AS Issued_Date_Plus, *
    FROM tblContributionRegister;
    Finally, you want to specify a date range. This is going to depend on where you are getting the date range from.

    If you want to Hard Code the Date Range the SQL will contain the criteria

    . Between #01/04/2010# And #31/03/2012#

    If you are picking the information up from a form, the Criteria would say

    . Between [Forms]![Form1]![Text0] And [Forms]![Form1]![Text1]

    N.B. There is a 3rd way which queries the dates upon running the SQL directly but I can't be sure on how to structure it so if someone else can tidy up the below...

    . Between [Start Date] And [End Date]


    I'm going to assume you are running the query from a form for the time being so your SQL will be;

    Code:
    SELECT tblContributionRegister.*, 
    IIf([Payment_Mode]="Monthly",DateAdd("m",3,[Issued_Date (date/time)]),
    IIf([Payment_Mode]="Quarterly",DateAdd("m",4,[Issued_Date (date/time)]),
    IIf([Payment_Mode]="Half Yearly",DateAdd("m",6,[Issued_Date (date/time)]),
    IIf([Payment_Mode]="Yearly",DateAdd("m",12,[Issued_Date (date/time)]))))) AS Issued_Date_Plus, *
    FROM tblContributionRegister
    WHERE (((
    IIf([Payment_Mode]="Monthly",DateAdd("m",3,[Issued_Date (date/time)]),
    IIf([Payment_Mode]="Quarterly",DateAdd("m",4,[Issued_Date (date/time)]),
    IIf([Payment_Mode]="Half Yearly",DateAdd("m",6,[Issued_Date (date/time)]),
    IIf([Payment_Mode]="Yearly",DateAdd("m",12,[Issued_Date (date/time)])))))) 
    Between [Forms]![Form1]![Text0] And [Forms]![Form1]![Text1]));
    This Code assumes that it's picking up the Start Date from a field called Text0 on a form called Form1, and the end date from a field called Text1 on the same form.
    Last edited by christyxo; 06-27-11 at 10:40.

  3. #3
    Join Date
    Jun 2011
    Posts
    4
    Thanks lot for this all effort, i will try it and than b back

  4. #4
    Join Date
    Jun 2011
    Posts
    4

    Smile

    Great work dear, I am really excited about access and your attention helped me lot.. thanks

    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;

    I have changed your provided code little, removed (date/time) from DateAdd("m",3,[Issued_Date (date/time)] and also changed values in “monthly” to 1 instead 3, “quarterly” 3 instead 4.
    After running this query it showed me all fields of tblContributionRegister and one extra field Issued_Date_Plus. 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 use easy and step by step method.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd suggest you move the payment mode to a separate table, define in there the the number of days that payment mode uses and then bring that value into the query

    why?
    it gets rid of the IIF and its associated complications and maintenance issues
    it makes the SQL easier to read, and easier to read code is easier to maintain
    it shifts the maintenance task to the users from the developer

    Code:
    SELECT my, comma, separated,  column, list,
    DateAdd("d",PaymentModes.NoDays,[Issued_Date]) as Issued_datePlus
    from listtblContributionRegister
    join PaymentModes on PaymentModes.Mode = listtblContributionRegister.Payment_Mode
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2011
    Posts
    4

    Question

    dear healdem, i cant understand how to do all this, kindly write all procedure in detail. thanks for your guidance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •