Anyone here designed an HR Database? (1 Viewer)

[lightray]

Hi,
Am looking for someone to talk about how Annual Leave might be calculated, given that an Employee can have a different entitlement over their employment with a company.

Mainly interested in calc'ing a daily entitlement, and not getting involved in any Payroll side of things.

Much appreciated, lightray

 

[Pat Hartman]

Usually in the US, vacation is acrued on a monthly basis. In that case, I would run an append query each month that adds a transaction with the monthly amount each month. That way you manage vacation much the same way you manage inventory. You have transactions that add vacation to an employee's account and you have transactions that subtract vacation from an employee's account. Using this method, you have auditability and it's easy to see if an action has happened and if it is correct.

If vacation is earned daily, that makes the transaction method a little bulky because of the number of records it would create but it is still the cleanest method. Is vacation earned 7 days a week, 52 weeks a year? You could still earn vacation daily but only post it monthly. That would minimize the transactions but you'd have to provide a method to calculate month-to-date amounts should the employee terminate or start mid-month so you can post a partial month if necessary.

 

[lightray]

Hi Pat, thanks for your input.
New Zealand administers a UK type standard when it comes to accruing [Annual Leave] more so when it's a government division. I've set my mdb up to query or requery every instant of a record change [Add or Change ]. There isn't likely to be more than 100 employees, but I can see this method will become weighty over a period, when all types of leave records are entered. I am considering archiving off old records.
Actually, as I write this I realise that I am only calc'ing figures for the [Current Leave Year] so may not be so bad.
When I put this post together I was mainly interested in the formula that would calculate the [Daily Increment] something like:
[DI] = [No. days worked in CLY] x ([Yearly Allowance] / 261)
261 being the number of working days in the year. the [CLY] is calculated minus weekends and Public Holidays. Is this a sound evaluation?
I was also interested in chatting to any other Developers who may have put an HR database together, to bounce ideas off

Any thoughts? lightray