Solved

Track employee leave time

Posted on 2006-10-27
15
509 Views
Last Modified: 2010-08-05
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
Comment
Question by:Galisteo8
15 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17823888

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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17824112
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 17825573
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 17826966
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 17836375
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17836531
> 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
 
LVL 8

Author Comment

by:Galisteo8
ID: 17836961
> "database dogma would dictate that it *not* be stored..."  Right, just checking.  :)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Author Comment

by:Galisteo8
ID: 17837018
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
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 17838829
> 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
 
LVL 8

Author Comment

by:Galisteo8
ID: 17844458
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 17845843
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 17845862
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
 
LVL 8

Author Comment

by:Galisteo8
ID: 17852850
Ah, wait... I had set an EmpID = PosID in the Where clause...
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 17853660
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17857196
Glad to help :)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now