Results 1 to 7 of 7
  1. #1
    rayhanbd is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    4

    Question House Rent Allowance calculation IN MS Access database

    How: House Rent Allowance Calculation: MS Access database
    A. Basic salary Up to TK 9,700, Get House Rent Allowance TK9,700*55% but Minimum House Rent Allowance TK 5,000.


    B. Basic salary Tk (9,701 to 16000), House Rent Allowance get 50% of Basic salary, But Minimum HRA TK 5,400,
    C. Basic salary Tk (16001 to 35500), House Rent Allowance get 45% of Basic salary, But Minimum HRA TK 8,000.
    D. Basic salary TK 35,501 to Up House Rent Allowance get 40% of Basic salary, But Minimum HRA TK 16000.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suggest have a table to contain the variables - otherwise with inflation over time you will need to adjust the calculation

    so table something like

    tblHRA
    salFrom..salTo……..PC....minHRA
    0...…......9700......55%..5000
    9700......16000....50%..5400
    16000....35500....45%..8000
    35500....Null...…..40%..16000

    then use a cartesian query (no join)

    Code:
    SELECT *, iif(E.Salary*HRA.PC<minHRA,minHRA,E.Salary*HRA.PC) AS HRAAmount
    FROM tblEmployees E, tblHRA HRA
    WHERE E.Salary>HRA.salFrom AND E.Salary<=nz(HRA.salTo,E.Salary)

  3. #3
    rayhanbd is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    4
    Quote Originally Posted by Ajax View Post
    suggest have a table to contain the variables - otherwise with inflation over time you will need to adjust the calculation

    so table something like

    tblHRA
    salFrom..salTo……..PC....minHRA
    0...…......9700......55%..5000
    9700......16000....50%..5400
    16000....35500....45%..8000
    35500....Null...…..40%..16000

    then use a cartesian query (no join)

    Code:
    SELECT *, iif(E.Salary*HRA.PC<minHRA,minHRA,E.Salary*HRA.PC) AS HRAAmount
    FROM tblEmployees E, tblHRA HRA
    WHERE E.Salary>HRA.salFrom AND E.Salary<=nz(HRA.salTo,E.Salary)
    I have Attached MY File Here, Pls Help me,
    Attached Files Attached Files

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in your tblHRA you have a big gap between the 3rd and 4th record, you have also not followed the salFrom=salTo of the previous record. Look at my example and try again

  5. #5
    rayhanbd is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    4
    Thank MR Ajax, I have flowed your example table but not success, I will be glad to you if you kindly apply your formula in my attached database,
    I am new in MS Access database.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In table tblHRA, the field PC is a TEXT type.
    I changed the field type to SINGLE, format percent, reentered the percents and .....success!

  7. #7
    rayhanbd is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    4
    Thanks Mr.Ajax & Mr ssanfu for your co-operation.
    It Works perfectly ,
    This forum help me to solve my problem.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 11-18-2016, 05:40 PM
  2. Replies: 3
    Last Post: 10-19-2015, 11:05 PM
  3. Replies: 6
    Last Post: 11-10-2012, 09:49 PM
  4. Creating Uniform allowance report
    By Jfort866 in forum Access
    Replies: 3
    Last Post: 06-13-2012, 11:35 AM
  5. Rent income stream
    By liles in forum Access
    Replies: 2
    Last Post: 01-24-2012, 08:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums