Hello. I am designing payslip generating forms. Idea is simple: I data for done jobs by employees. That table is called 'EmpHours'. This stores important fields:
- ID (pk)
- EmployeeID (fk - employee information (hourly rate most importantly) from 'Employees' table)
- SaleID (fk - links to sale)
- RoleID (fk - shows what role an employee was doing for that job)
- PayslipNumber (will be added once payslip is submitted to pin done jobs to certain payslip)
- Hours (hours done on that day for that job)
- Miles (amount of miles traveled by an employee)
- Expenses (calculated field of 0.4 * Miles to get expense amount which will be paid back to an employee)
- Salary (shows how much an employee earns for 1 hour of work)
- isCharged (tickbox to mark if that jobs is already been charged on any of payslips)
- TaxYear (tax year starts from 1st April and runs through 12 months, to use it in a query to pull data I store it in this table)
This is the ONLY data I store in database about employees and their done jobs. The most important fields are HOURS, EXPENSES and SALARY.
To calculate Holiday Pay the following calculation will be done (per employee):
Sum of Total Hours worked * 0.127 (12.7%) = Total hours available for Holiday Pay
In my query I use: TotalHolidayHours: Nz(Round(Sum([EmpHours].[Hours]*0.127),1),0)
Holiday Pay can be requested by an employee, it will not be calculated for them every month. For example:
John Smith has worked for 3 months. He has done total 50 hours of work which is 50 * 12.7% / 100 = 6.4 hours available (rounded up).
Now if an employee requests holiday pay for 3rd month payslip, I must be able to select how many hours he wants to select among all the accrued hours. Let's say he wants to select 5 hours from holiday pay. Amount paid for him would be 5 * hourly rate (5 * £8 = £40). Now this amount will be added to total payments which includes Salary, Expenses and Holiday Pay (IF requested).
Now I also need to store this data somehow to my 'Payslips' table, so for next payslip IF an employee has requested holiday pay already, then the requested amount will be next time subtracted from total hours accrued and I need to get result in my subform that shows how much hours are now (next month) available for him. (I hope I am doing this right). Payslips table has important fields:
-ID (pk)
- HolidayPayUsed (it stores the value how many hours were selected on that payslip)
- HolidayTaken (checkbox to show if holiday pay was selected on that payslip, if no then nothing will be added to total payments or gross pay, if yes then will be added.)
- EmployeeID (fk)
- PayslipNumber (will be generated when payslip is submitted)
- StatusID (will be used to select subforms form query depending on this value, because I use same form to generate payslips and if it's already generated then to view payslips, but only in read only mode and it uses different queries for 3 subforms I have there)
- TaxPaid, EmpNIC, Overpayment (these are deduction fields which will be stored here as well and will be used in calculated field 'TotalDeductions' and it will be subtracted from TotalPayments)
I don't store TotalPayments, Salary, Mileage expenses etc that are in EmpHours table in my 'Payslips' table because I use queries to show that data on Payslips.
My main problem is that I can't get my query working for holiday pay. Right now I have only one query for that which is:
Code:
SELECT EmpHours.EmployeeID, Roles.RoleLong, Nz(Round(Sum([EmpHours].[Hours]*0.127),1),0) AS TotalHolidayHours, Employees.HourlyRate, Sum([Hours])*[HourlyRate] AS Salary
FROM Roles INNER JOIN (Employees INNER JOIN EmpHours ON Employees.ID = EmpHours.EmployeeID) ON Roles.ID = EmpHours.RoleID
GROUP BY EmpHours.EmployeeID, Roles.RoleLong, Employees.HourlyRate, EmpHours.isCharged, EmpHours.TaxYear
HAVING (((EmpHours.isCharged)=True) AND ((EmpHours.TaxYear)=Year(Date())))
This only gives me total holiday hours for that employee. But every time I make new payslip for that employee. I need to get total available hours for holiday pay to show me value if holiday pay is already requested. So let's say in my example above if he requested 5 hours for 3rd month, then if he requests it 5 months later again then this 5 hours must be subtracted from current total available.
I hope someone can understand what I need and it was good enough explained.
Thanks!