Results 1 to 13 of 13
  1. #1
    RakB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    11

    Custom Salary Management Application

    Hello,

    I am trying to build a customized salary managment application for a small company. Until now excel spreadsheet was used but as company is growing, making changes is becoming difficult in excel. Hence, Access. I have some basic relational database design knowledge.

    Here is a brief description about the salary calculations and process
    1. Total Salary = [Attendance Bonus] + base salary + (# of Overtime Hours / 8)*1.5*basesalary - (# of absent days / 30)*base salary - Advance Recovered - Provident fund (PF) amount

    An employee get a fixed amount Attendance bonus if they dont miss any working days of the month
    An employee can request certain amount of money (borrow) known as "Advance Borrowed"
    An employee can payback certain portion of money borrowed known as "Advance Recovered"
    All the advance amounts are independent of monthly salary

    2. The salary is paid every month, but the total salary is paid in two installments - once beginning of the month and next during third week of the month (this is for some employees only - optional)

    3. certain employees can have "holiday exemption" where one to two days per month can be taken off without counting as absent - no deductions from salary

    4. Certain employees can opt for fixed PF amount to be deducted from their salary (it goes into their retirement fund). However, this is optional

    5. An yearly pay rise (increment) is given to employees



    So far, I have created five tables

    tblEmployeeInfo
    tblMonthlySalary
    tblPFInfo
    tblIncrementSummary
    tblAdvanceSummary

    1. Employee information table (tblEmployeeInfo) contains following fields
    empID - Auto number
    empFName (first name) - short text
    empLName (last name) - short text
    dOJ (date of join) - date/Time
    address - short text
    phone - short text (with input mask for validation)
    holidayEx - number (can only be 0, 1 or 2)
    department - a custom built lookup list
    baseSalary - number

    2. Employee monthly salary table (tblMonthlySalary) contains following fields
    sID - Auto number
    empID (foriegn key) - mapped to empID in tblEmployeeInfo
    absentDays (# of absent days) - number
    OT (overtime) - number
    monthID (month) - Number (from 1 to 12)
    year - Number
    pay1Date - Date/Time
    pay1Amt (first payment) - Number
    pay2Date - Date/Time
    pay2Amt (second payment) - Number

    3. Employee PF information (tblPFInfo) contains following fields


    pfID - Auto number
    empID (foriegn key) - mapped to empID in tblEmployeeInfo
    pfExemption - Yes/No
    pfAmt (PF Amount) - Number

    4. Employee salary increment information (tblIncrementSummary) has following fields
    iID - Auto number
    empID (foriegn key) - mapped to empID in tblEmployeeInfo
    incrementDate - Date/Time
    incrementAmount - Number

    5. 4. Employee advance summary information (tblAdvanceSummary) has following fields
    aID - Auto number
    empID (foriegn key) - mapped to empID in tblEmployeeInfo
    advBorrowDate - Date/Time
    advBorrowAmt - Number
    advRecDate - Date/Time
    advRecAmt - Number


    My objectives:

    1. I would want to provide user a form. I am thinking form to have following options:
    a. filter and search employees by their name and department
    b. User types in # of absentDays, OT, advanceRecovered, advanceBorrowed information. As a result, the following information shouls be calculated an shown in the form:
    • Attendance Bonus (if applicable, else 0)
    • Base Salary
    • OT Amount (amount obtained by working overtime)
    • Absence Loss (amount lost due to absence - this has to taken into account holiday exemption i.e. if an employee has holiday exemption = 1, the number of absent days will be reduced by 1)


    This is my first attempt at the form design:
    Click image for larger version. 

Name:	Form_Design.JPG 
Views:	65 
Size:	117.1 KB 
ID:	40821

    Note: There is no functionality implemented for the buttons! This is just my visualization (I need to know how to achieve this)


    2. The application should have basic reporting (total expenditure at end of each month, # of absentees, etc.) with some simple charts - This is not important right now

    3. There are plans in the future to run this as a web based application in the future. What considerations should i take during this inital stages? (the web implementation is not imminent, but i want to make sure it can be easily converted to a webapplication with few modifications)



    My questions:

    1. Is my database design adequate? Do i need to modify anything?
    2. I have a populated tblEmployeeInfo with about 50 dummy records. I am unsure as to how to proceed next. I have limited knowleedge about running queries. Where should I start?
    3. Since there are usually two payments made, How can I ensure that second payment is valid only when there is a balance after payment 1 and payment 1 + payment 2 should be equal to total slary computed?

    Any pointers will be greatly appreciated!

    PS: I am using office 365
    Last edited by RakB; 02-06-2020 at 02:12 PM.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    1)
    What would you do if there were 2 employees named John Smith in the same department? (we actually has 2 people named Jim Darling in the same department)
    Do the employees have something like a badge number or Employee number?
    I would have another table for departments (tblDepartments) NOT a lookup field in a table.



    Would you post your dB?


    (Nice description BTW)

  3. #3
    RakB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    11
    Quote Originally Posted by ssanfu View Post
    1)
    What would you do if there were 2 employees named John Smith in the same department? (we actually has 2 people named Jim Darling in the same department)
    Do the employees have something like a badge number or Employee number?
    That is valid point. There is no employee badge number. The company is the past has used additional letters in last name to differentiate employees with same name within same department.

    I would have another table for departments (tblDepartments) NOT a lookup field in a table.
    The departments in the company are fixed. They won't change in the likely future.

    Would you post your dB?
    dB is just tables and a form which does nothing. I could upload the dB if you could help me / provide me with few starting points on constructing and executing queries.

    I want to connect all these things together and create an application with just forms visible to user. I have no idea where to start or how to construct queries to get this done.

    Where should i start?

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    (# of working days / 30)*base salary
    It looks like calculating the main part of monthly salary, where base salary is salary when employee is working full 30-day month without free weeends. E.g. with 20 working days in month and base salary = 1000, this will be 1000*20/30 = 666.67
    Code:
    (# of Overtime Hours / 8)*1.5*base salary
    Let's go for extreme, and assume someone was really hard worker and worked additional 8 hours overtime every workday. This results in 20*8 = 160 overtime hours. Overtime pay will be 1.5*1000*160/8 = 30000!
    Doesn't it really look like something is off here? Or can I work for your company? :-)

  5. #5
    RakB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    11
    There was a mistake in the equation. It is fixed now.

    As for overtime, the overtime hours are fixed by the company (usually weekends) and are limited. Its optional for employees and its rare that employees work all week and most weekends

  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
    Did you use pencil and paper/whiteboard/sticky notes to design your dB before jumping into Access?

    "Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.


    Do a "Compact and Repair", then Zip (compress) the dB, then attach it

    How to attach files

  7. #7
    RakB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    11
    Quote Originally Posted by ssanfu View Post
    Did you use pencil and paper/whiteboard/sticky notes to design your dB before jumping into Access?

    "Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.


    Do a "Compact and Repair", then Zip (compress) the dB, then attach it

    How to attach files
    I have attached the dB.

    Salary_Management_App.zip

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I haven't forgotten about you... still trying to understand how and the order you do things and looking at the table designs.


    Quote Originally Posted by RakB View Post
    1. Total Salary = [Attendance Bonus] + base salary + (# of Overtime Hours / 8)*1.5*basesalary - (# of absent days / 30)*base salary - Advance Recovered - Provident fund (PF) amount

    So if the work month is October, Nov 1 you would calculate the pay and give 1/2 in the first week and the 2nd half in the 3rd week?
    Attendance Bonus Monthly?
    base salary Monthly amount
    (# of Overtime Hours / 8)*1.5*basesalary Monthly or half a month?
    (# of absent days / 30)*base salary - absent days Monthly or half a month?
    Advance Recovered Monthly or half a month?
    Provident fund (PF) amount Monthly or half a month?








    Quote Originally Posted by RakB View Post
    4. Employee salary increment information (tblIncrementSummary) has following fields
    iID - Auto number
    empID (foriegn key) - mapped to empID in tblEmployeeInfo
    incrementDate - Date/Time
    incrementAmount - Number
    Is the "incrementAmount" field (tblIncrementSummary) an amount to add to the (tblEmployeeInfo) "baseSalary" field in the calculations or is the "baseSalary" field amount changed to "baseSalary" + "incrementAmount"?

  9. #9
    RakB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    11
    Quote Originally Posted by ssanfu View Post
    I haven't forgotten about you... still trying to understand how and the order you do things and looking at the table designs.



    So if the work month is October, Nov 1 you would calculate the pay and give 1/2 in the first week and the 2nd half in the 3rd week?
    Attendance Bonus Monthly?
    base salary Monthly amount
    (# of Overtime Hours / 8)*1.5*basesalary/30 Monthly or half a month?
    (# of absent days / 30)*base salary - absent days Monthly or half a month?
    Advance Recovered Monthly or half a month?
    Provident fund (PF) amount Monthly or half a month?

    Yes, the salary for the work done during month of october is given in first and 3rd week of november. Any portion of the total salary computed can be given suring first week and the remaining during 3rd week (doesn't need to be exactly half).

    For example, Employee A has a base salary of $5000 per month. For the month of october:

    # of absent days: 3
    # of overtime hours: 4
    Holiday exemption: Yes
    # of holidays exemption: 1

    Let us consider that employee A has an outstanding advance of $4000 (amount borrowed apart from monthly salary)

    The total salary computation is as follows:
    1. Attendance Bonus = $0 (considering holiday exemption, the total number of absent days for employee A is 3-1 = 2. This is greater than 0)
    2. Overtime Amount: (4/8)*1.5*(5000/30) = $125 [There was a typo in the formula, I have corrected it in your table above]
    3. Absence Loss: 3*(5000/30) = $500
    4. Advance Recovered: $500 (employee wishes to pay back $500 towards his/her outstanding advance of $4000)
    5. Advance Borrowed: $0
    6. PF amount: $800 (certain amount of money, in this case $800, is set aside every month for employee's PF account. This amount will be transferred to their PF account)

    Thus, the total salary for month of october becomes = 0 + 5000 + 125 - 500 - 500 - 800 = $ 3325

    Now, this amount is paid in first and third week of november. Say, about $2500 is paid in first week and remaining $825 is paid in 3rd week. Also, the outstanding advance oof the employee has to be updated in advance table ($4000 - $500 = $3500)

    And to clarify, all components of the salary in your table are "monthly".

    Also, any advance borrowed doesn't become part of monthly salary. It is seperatley added to any outstanding advance in the advance table. on the other hand, any advance recovered gets subtracted from the monthly salary as shown in example above.

    Hope this clarifies.

    Is the "incrementAmount" field (tblIncrementSummary) an amount to add to the (tblEmployeeInfo) "baseSalary" field in the calculations or is the "baseSalary" field amount changed to "baseSalary" + "incrementAmount"?
    Increment is usually given once a year. Both methods are acceptable. It would better to update the "baseSalary" field to "baseSalary" + "incrementAmount".

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Much better idea of the process now.

    One more question. Do you need/want to keep a history of the "baseSalary" increment?
    base Salary Amount
    Increment Amount
    Raise date
    $3500 (hired) $0 6/15/2011
    $3620 $120 6/1/2012
    $3760 $140 6/1/2013
    $3910 $150 6/1/2014
    (field order doesn't matter)

  11. #11
    RakB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    11
    Quote Originally Posted by ssanfu View Post
    Much better idea of the process now.

    One more question. Do you need/want to keep a history of the "baseSalary" increment?
    base Salary Amount
    Increment Amount
    Raise date
    $3500 (hired) $0 6/15/2011
    $3620 $120 6/1/2012
    $3760 $140 6/1/2013
    $3910 $150 6/1/2014
    (field order doesn't matter)
    Yes, that would be useful.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I haven't forgotten about you. Life got in the way for a while - work, snowplowing, etc.

    Here is an update so you can see where I am headed. I've been trying to use your form layout, but it is modified somewhat.
    I'm trying to ensure that all of the fields are being saved to be able to to the calcs.

    One question - you have "# OT Days". shouldn't that be "# OT Hours"?

    I still think I've missed a data entry field for the calculations. Still working on it.....


    Let me know if I'm off track...
    Attached Files Attached Files
    Last edited by ssanfu; 02-27-2020 at 07:53 PM.

  13. #13
    RakB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    11
    Steve,

    Thank you for the update. The Form looks like what I had imagined before starting this project. I like the subforms in some of the forms to see the related data easily.
    I am going through the queries and VBA code to understand how everything is working.

    Regarding the OT days or hours, the correct formula is: (# of Overtime Hours / 8)*1.5*basesalary/30
    Overtime is usually specified in hours. It is then converted into days by dividing by 8 (1 day = 8 hrs) and this is used for calculating salary. Overtime pay is 1.5 times the normal pay as seen in the above formula.

    Everything looks good upto the salary calculation part. Is there a simple way to remove an employee or mark them inactive but still have their salary history?

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

Similar Threads

  1. Replies: 5
    Last Post: 03-14-2019, 03:01 AM
  2. Replies: 4
    Last Post: 10-01-2016, 04:15 PM
  3. School Management application
    By tahirsatti in forum Forms
    Replies: 3
    Last Post: 10-04-2013, 12:19 PM
  4. Where to Put Instructor Salary?
    By alpinegroove in forum Database Design
    Replies: 54
    Last Post: 01-03-2012, 05:37 PM
  5. Volunteer management application
    By NOTLguy in forum Access
    Replies: 3
    Last Post: 10-01-2010, 12:04 PM

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