Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 565
  • Last Modified:

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
0
Galisteo8
Asked:
Galisteo8
2 Solutions
 
jefftwilleyCommented:

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
0
 
nmcdermaidCommented:
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.)
0
 
Patrick MatthewsCommented:
Hi Galisteo8,

In addition to the previous good suggestions...

You might think about:

tblEmployees
---------------------------------------------------------------------
EmpID (PK)
EmpLName
EmpFName
EmpMName
PosID (FK)
HireDate
<others>

tblPositions
---------------------------------------------------------------------
PosID (PK)
PosDescr

tblAccrueSched   <-- holds the schedule of accrual rates, by position and tenure
---------------------------------------------------------------------
RuleEffectDate (PK)
PosID (PK, FK)
Tenure (PK)
AccrueRate

tblLeave   <-- holds leave time reasons: vacation, sick, personal, comp time, etc.
---------------------------------------------------------------------
LeaveID
LeaveDescr

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

That should give you something to get started with :)

Regards,

Patrick
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Galisteo8Author Commented:
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...
0
 
Galisteo8Author Commented:
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?
0
 
Patrick MatthewsCommented:
> 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
0
 
Galisteo8Author Commented:
> "database dogma would dictate that it *not* be stored..."  Right, just checking.  :)
0
 
Galisteo8Author Commented:
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...?
0
 
Patrick MatthewsCommented:
> 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?

Exactly.  Trying to design for flexibility :)

BTW, I forgot a field on tblAccrueSched: LeaveID, which should be part of the primary key as well as a foreign key.

> 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...?

I would probably run a regular process to insert the accrual records; if the accrual comes monthly, do it monthly.
The query would be something like:


INSERT INTO tblLeaveEntries (EmpID, EntryDate, LeaveID, LeaveAmt)
SELECT e.EmpID, [Accrual Date] AS EntryDate, l.LeaveID, a.AccrueRate AS LeaveAmt
FROM tblEmployees AS e INNER JOIN
    (tblPositions AS p ON e.PosID = p.PosID INNER JOIN
    (tblAccrueSched AS a ON p.PosID = a.PosID INNER JOIN
    tblLeave AS l ON a.LeaveID = l.LeaveID))
WHERE l.LeaveDescr = "Vacation" AND
    a.RuleEffectDate = (
        SELECT Max(a2.RuleEffectDate)
        FROM tblAccrueSched AS a2
        HAVING a2.RuleEffectDate <= [Accrual Date]) AND
    a.Tenure = (
        SELECT Max(a2.Tenure)
        FROM tblAccrueSched AS a2
        HAVING a2.Tenure <= (([Accrual Date] - e.HireDate) / 365))


Accrual Date was used as a parameter here.  That SQL may not be 100% correct, but it's a starting point.

Patrick
0
 
Galisteo8Author Commented:
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?
0
 
Galisteo8Author Commented:
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...
0
 
Galisteo8Author Commented:
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);
0
 
Galisteo8Author Commented:
Ah, wait... I had set an EmpID = PosID in the Where clause...
0
 
Galisteo8Author Commented:
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.
0
 
Patrick MatthewsCommented:
Glad to help :)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now