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:
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