Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Track employee leave time

Posted on 2006-10-27
15
Medium Priority
?
564 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 93

Accepted Solution

by:
Patrick Matthews earned 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 93

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
 
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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 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 93

Expert Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

618 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