Link to home
Start Free TrialLog in
Avatar of Galisteo8
Galisteo8

asked on

Track employee leave time

I’m trying to set up a small Access database with forms/reports for helping someone track sick and annual leave time.  Basically, she wants to have a datasheet form showing all employees where, after every 2-week pay period, she can enter the leave time that each employee has taken, and that form will also show her (dynamically) the amount of leave accrued during that same period and what the new balances are for each employee.  A second form will also be required which will show each employee as a separate record, with name, contact info, etc., plus a subform showing their leave history across all pay periods (including the running balance, like a checkbook with debits and credits).

She should be able to edit the leave data from either form.

I think I need 4 tables – employees, sick time, annual time, and pay periods – but I’d appreciate some thoughts regarding a good way to go about this table structure, table relationships, etc. – especially if anyone has done this kind of project before.

Thanks,
Galisteo8
Avatar of jefftwilley
jefftwilley
Flag of United States of America image


When it comes to tracking leave balances and time off...a lot of things come into play.
Holidays, weekends, partial days. A table of these days, or the opposite of them comes in handy. Saves on having to factor on what is and isn't a weekend or holiday.

A good rule of thumb is you keep similar data in one table. In this case if you consider any time off to be time off, then your sick time, vacation time, whatever chargeable time you can come up with should be kept together, and have a field to identify the "type" of time.

Use a key for your pay periods. MEaning. for pay period between 01/01/2007 and 01/15/2007...give this a numeric value. Like period 1. keep that count going for all future date ranges. That way instead of always having to do date calculations, you can use that value in place of the range and do simple math.

What about requested time off? Do you plan on trying to capture who might want to take vacation at Christmas? if you have positions that HAVE to be covered during the holidays, then you want to make sure that both of your key people aren't off at the same time. If you do decide to provide this kind of planning, then you want to make sure you have a way to approve or disapprove requests. An approved request might eventually turn into an actual range. You might be able to use that simple fact to help you cut down on manual calculation.

Other than that...take your time. There's a lot of really good ideas on this site. And lots of smart people that can help you out.

J
Avatar of nmcdermaid
nmcdermaid

I definitely agree that sick time and annual time need to go in the same table with a 'type' field. Then this can then be easily extended to store long service leave, and whatever other kinds there are (here in australia: bereavement, maternity etc.)
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Galisteo8

ASKER

Thanks for the suggestions thus far.

We are using Outlook and Exchange Server to manage our actual calendars (scheduled leave, flex scheduling, etc.), so the Access database won't have to worry about weekends, holidays, scheduling, etc., since it is not intended to replace this functionality.  Rather, it's will only be for tracking ACTUAL used leave (i.e. after it's been used) against ACCRUED leave.

I agree that each pay period needs its own ID key.  There is no reason to be calculating dates against eachother for this database.  Also, we don't need to handle partials days either -- I've been instructed to use a flat accrual rate PER PAY PERIOD, not per hour worked, for both the sick leave accrual and for the annual leave accrual.  (What I'm calling Annual leave is really our generic "Paid Time Off", or PTO, used for all kinds of non-sick leave. So we have only two types: sick and PTO.)

I'm sure I'll have a few follow-up questions as I proceed...
Patrick...

You suggested:

"
tblLeaveEntries
---------------------------------------------------------------------
EmpID (PK, FK)
EntryDate (PK)
LeaveID (PK, FK)
LeaveAmt   <-- positive is earned, neagtive is used
"

How do you envision that such a field as LeaveAmt would be used in this table?

In our case, each employee's accrued leave and balance will be calculated for each pay period, regardless of wether any leave time was used.  On top of that, some employees will have also USED some leave time.  For example, I imagine that each row of the employee's subform would look something like this:

|  Pay Period   |  Sick Accrued | Sick Used | Sick Balance | PTO Accrued | PTO Used | PTO Balance |
|    10/6/06     |       2.8         |       3.0      |       57.5      |        3.2        |       0.0    |       78.0       |
|    10/20/06   |       2.8         |       0.0      |       60.3      |        3.2        |       8.0    |       73.2       |
|    11/3/06     |       2.8         |       0.0      |       63.1      |        3.2        |       0.0    |       76.4       |

This subform would likely be based on a query, but will need to draw on TWO values from the table... right?  Or are you suggesting that the accrual and the balance just be calculated "on the fly" for the form, but not stored anywhere?
> How do you envision that such a field as LeaveAmt would be used in this table?

As employees accrue time, have records in the table.  So, let's say I accrue 13.33 hrs of PTO
and 3.33 hrs of sick time each month.  I would have two accrual records, with +13.33 and +3.33.

If I used any time, then I would have records recording the time used.

As for storing a balance, database dogma would dictate that it *not* be stored, as you should not
store derived values, but rather calculate them on demand.

Patrick
> "database dogma would dictate that it *not* be stored..."  Right, just checking.  :)
Patrick,

I presume you separate the Accrual Rates into their own table, with an "effective date" key, in order to allow for rates to change in the future without messing up calculations on reports for older (pre-change) date periods?

In practice, how does one make that work?  The query behind the subform, for example, would have to match dates for each row of data...?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK... So this process would be launched by the user, it would ask her for the Accrual Date (or, in our case, the ending date of the pay period), and then would insert into tblLeaveEntries a new record for EACH EmpID for EACH leave type, with the date she entered:

|     EmpID     |     PeriodEnd     |     LeaveID     |     LeaveAmt     |
|        3         |        10/6/06      |         1          |         5.50         |
|        3         |        10/6/06      |         2          |         3.40         |
|        5         |        10/6/06      |         1          |         5.50         |
|        5         |        10/6/06      |         2          |         3.40         |
|        10       |        10/6/06      |         1          |         5.50         |
|        10       |        10/6/06      |         2          |         3.40         |
etc.

Obviously I'll need to tweak somewhere to have TWO separate entries -- one for each leave type, for each EmpID -- but is this the gist of the code you posted?
Patrick,

So far, it seems to be trying to do what I just described above. HOWEVER, I'm only getting one record created in the tblLeaveEntries table, rather than a record for each employee...
Also... I had to change the query due to a JOIN error. It now reads as follows:

INSERT INTO tblLeaveEntries ( EmpID, PeriodEnd, LeaveID, LeaveAmt )
SELECT tblEmployees.EmpID, PeriodEnd, tblLeave.LeaveID, tblAccrueSched.AccrueRate AS LeaveAmt
FROM tblLeave INNER JOIN ((tblPositions INNER JOIN tblEmployees ON tblPositions.PosID=tblEmployees.PosID) INNER JOIN tblAccrueSched ON (tblPositions.PosID=tblAccrueSched.PosID) AND (tblEmployees.EmpID=tblAccrueSched.PosID)) ON tblLeave.LeaveID=tblAccrueSched.LeaveID
WHERE tblLeave.LeaveDescr = "Sick" AND
    tblAccrueSched.RuleEffectDate = (
        SELECT Max(tblAccrueSched.RuleEffectDate)
        FROM tblAccrueSched
        HAVING tblAccrueSched.RuleEffectDate <= PeriodEnd);
Ah, wait... I had set an EmpID = PosID in the Where clause...
That'll do it for this phase.  Thanks for the help.  I've modified Patrick's table structure and code suggestion to get me started.  I'll post other threads as I move forward. (Actually, I already have!)

Thanks.
Glad to help :)