Results 1 to 6 of 6
  1. #1
    crxftw is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    30

    Payslip Holiday Pay design

    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!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Without seeing an example database and seeing your relationships (though all your tables seem to be in order) Don't try to do this in one query. Create a sub query that has the totals for each employee for non-holiday pay that they've requested which would be something like

    SELECT EmployeeID, Sum(HolidayPayUsed) as NonHolPay FROM Payslips GROUP BY EmployeeID

    This would give a query that shows all employees and how much holiday pay they've used then you could subtract it from your total (which seems to be what you're after)

  3. #3
    crxftw is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    30
    Hello. Thanks for reply.

    Yes that's actually what I tried to do now. I have this select query from Payslips table that sums HolidayPayUsed and groups them by EmployeeID. But I can't figure out where I use this query now? Do I use it in my other query now and do the calculation on form fields?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You would use it whenever you want to figure out how much time/money a person has remaining they can use. I would suggest that in Payslips table you also track HOURSUSED. Their pay rate may change over time and if you only store the currency value you really are not tracking the number of hours they have used.

  5. #5
    crxftw is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    30
    HolidayPayUsed is Hours value already not currency.

    Anyway the problem is right now, that when I use that subquery in my main query, say for example I have 4 test payslips right now. Available hours goes like that: 0 hours - First month, 2 hours - Second month, 8.6 hours - Third month, 9.9 hours - Forth month

    Now when say I request holiday for Forth month, say 1 hour, then I need my query criteria to only subtract it from payslips that follow to this one (5th, 6th etc), right now if I have set 1 hour for that then when I open up Second month payslip, I see available hours '1', because it subtracts 2-1, but I don't need that calculation here, I need only the sum of HolidayPayUsed that were made before payslip. Right now my criteria is <[Forms]![frmPayslipDetail]![txtPayslipNumber] that selects all payslips that has number lesser than current one (for example for payslip #305 every previous will be selected, EmployeeID is used in Master and Child field for subform so I don't set Employee as criteria in my query.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Then create your payslips lookup query to not just be EmployeeID and HoursUsed but EmployeeID, PaySlipNumber and Hoursused and link to your data on both employeeID and payslipnumber. I think then you could probably sum it however you wanted.

    Is there any chance you could upload a sample of your database and say exactly buttons you're clicking on your form to get to where you want your solution to handle this.

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

Similar Threads

  1. Simple Holiday bookings database?
    By darkan99el in forum Access
    Replies: 2
    Last Post: 03-11-2011, 11:25 AM
  2. Help please in design!
    By Sleepymum in forum Access
    Replies: 1
    Last Post: 01-25-2011, 11:12 AM
  3. Email Payslip (report) to individual employee
    By batowiise in forum Reports
    Replies: 1
    Last Post: 11-09-2010, 05:08 AM
  4. DB Design
    By Merkava in forum Database Design
    Replies: 2
    Last Post: 11-10-2009, 05:51 PM
  5. function for national holiday
    By barkarlo in forum Access
    Replies: 0
    Last Post: 12-18-2006, 03:39 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