Annual Leave Database (1 Viewer)

TriciaLatchman · Aug 19, 2017

Hi Guys,

I am trying to create a database that track annual leaves for employees by entitlements.

I have created a table named tblType_of_Leave with the following fields
(PK) LeaveTypeCode = AutoNumber
LeaveTypeDescription=Short Text
Days Entitled=Number

Leave Type Days Entitled

Vacation Leave 20 Days for 1-Year Contract
40 Days for 2-Year Contract
60 Days for 3-Year Contract
0 Days for Month to Month
0 Days for Probation

Personal Leave 7 Days for 1-Year Contract
7 Days for 2-Year Contract per Contract Year
7 Days for 3-Year Contract per Contract Year
0 Days for Month to Month
0 Days for Probation

Sick Leave 14 Days for 1-Year Contract
14 Days for 2-Year Contract per Contract Year
14 Days for 3-Year Contract per Contract Year
0 Days for Month to Month
0 Days for Probation

Maternity Leave 70 Days for 1-Year Contract
70 Days for 2-Year Contract per Contract Year
70 Days for 3-Year Contract per Contract Year
0 Days for Month to Month
0 Days for Probation

Paternity Leave 2 Days for 1-Year Contract
2 Days for 2-Year Contract per Contract Year
2 Days for 3-Year Contract per Contract Year
0 Days for Month to Month
0 Days for Probation

Compassionate Leave 3 Days for 1-Year Contract
3 Days for 2-Year Contract per Contract Year
3 Days for 3-Year Contract per Contract Year
3 Days for Month to Month
3 Days for Probation

No Pay Leave 0 Days

How do i setup table to calcuate Entilement base on infor above?

Table Contact Term are as follow:
PK ID AutoNumber
Contract Term Short Text

ID ContractTerm
1 1-Year
2 2-Years
3 3-Years
4 Month-to-Month
5 Probation

I am relatively new to Access an really need some advice.

Requirements:
Form to select employee from tblEmployee
select LeaveType Taken from tblType_of_Leave
enter number of Days Taken

Report to show List of Employees with their LeaveType,
Entitlements (Days) for each Leave Type,
Number of Days Taken for each Leave Type,
And Balance of Days for each Leave Type,

MarkK · Aug 19, 2017

This looks like a many-to-many relationship. You have a fixed set of contract types, a fixed set of leave types, and then pairs of those items are linked by a quantity of days that is valid for that pair.

So you need tables like...

tContractType
ContractTypeID (Primary Key)
ContractTypeName

tLeaveType
LeaveTypeID (PK)
LeaveTypeName

tContractLeave
ContractLeaveID (PK)
ContractTypeID (Foreign Key)
LeaveTypeID (FK)
Days

hth
Mark

arnelgp · Aug 19, 2017

You add the contract term I'd as foreign key to your leave type table, therefore it is easy to calculate. Employee table should include contract term id. Another table, that is the actual employee leave table should include employee id, leave type id, two date field for start of leave and return date from leave. You can subtract these two dates for the actual leave taken. You can then compare the actual days taken to your leave type table.

Sorry only using my cp.

TriciaLatchman · Aug 21, 2017

I have created the tables but i am still not sure how i calculate the entitlements based on the different contract terms. Is is possible to do a quick sample. i think i will be better able to understand there. thanks

MarkK · Aug 21, 2017

Have you entered the data into the tables too?

To get the entitlement you query the relevant row from the tContractLeave table with SQL like...

SELECT Days
FROM tContractLeave
WHERE LeaveTypeID = [Enter the Leave Type ID]
AND ContractTypeID = [Enter the Contract Type ID]

A coded function might look like....

Code:

Function GetLeaveEntitlement(LeaveTypeID as long, ContractTypeID as long) as long
   Dim SQL as string
   SQL = _
      "SELECT Days FROM tContractLeave " & _
      "WHERE LeaveTypeID = " & LeaveTYpeID & " " & _
         "AND ContractTypeID = " & ContractTypeID 
   With CurrentDb.OpenRecordset(SQL)
      If Not .EOF Then GetLeaveEntitlement = .Fields("Days")
      .close
   End With
End Function

See what's going on there???
hth
Mark

TriciaLatchman · Aug 21, 2017

Hi MarkK,

I an really new to access....i am tying to figure out where i put in the actual No. of days for each leave type. can you please clarify?

Leave Type Days Entitled

Vacation Leave 20 Days for 1-Year Contract
40 Days for 2-Year Contract
60 Days for 3-Year Contract
0 Days for Month to Month
0 Days for Probation

Personal Leave 7 Days for 1-Year Contract
7 Days for 2-Year Contract per Contract Year
7 Days for 3-Year Contract per Contract Year
0 Days for Month to Month
0 Days for Probation

Sick Leave 14 Days for 1-Year Contract
14 Days for 2-Year Contract per Contract Year
14 Days for 3-Year Contract per Contract Year
0 Days for Month to Month
0 Days for Probation

Maternity Leave 70 Days for 1-Year Contract
70 Days for 2-Year Contract per Contract Year
70 Days for 3-Year Contract per Contract Year
0 Days for Month to Month
0 Days for Probation

Paternity Leave 2 Days for 1-Year Contract
2 Days for 2-Year Contract per Contract Year
2 Days for 3-Year Contract per Contract Year
0 Days for Month to Month
0 Days for Probation

Compassionate Leave 3 Days for 1-Year Contract
3 Days for 2-Year Contract per Contract Year
3 Days for 3-Year Contract per Contract Year
3 Days for Month to Month
3 Days for Probation

MarkK · Aug 21, 2017

Did you see and understand the three tables I described in post #2? I think these are the minimum tables and fields you need to model this problem.

You have three types of objects here, the leave, the contract, and the entitlement. Also, the entitlement exists at the intersection of each contract/leave combination, correct? To model this in a database takes three tables, and it's called a many-to-many relationship.

hth
Mark

MarkK · Aug 22, 2017

Hi Tricia,
Your ContractLeave table has no data in it. In your posts #1 and #6 you showed detailed data about the leave types, contract types, and entitlements, and it looks like you've entered the leave types and the contract types correctly, but the tblContractLeave table still has no data in it.

That table should contain a row for each combination of contract type and leave type for which there is a valid entitlement, so if there are 140 days for a maternity leave for a 2-year contract, I would expect to see a row (looking at your existing data) where ContractTypeID = 2, LeaveTypeID = 2, and Days = 140.

Also, we are not yet at the stage where we can link in employees, so that field does not belong in tblContractLeave. We are still only building the tables from which you'll look up the entitlement by contract and leave.

hth
Mark

TriciaLatchman · Aug 22, 2017

Hi Mark,

I understand, populating data into ContractLeave table.

Regards

TL

TriciaLatchman · Aug 22, 2017

Hi Mark,

See attached

ContractLeave table with data. Can you please advise how i proceed now..thanks

MarkK · Aug 22, 2017

Hi Tricia,
Now you have a system that satisfies the requirements you posted about. You have encapsulated the information in tables, and each ContractType/LeaveType combination has an entitlement value that you can look up...

Code:

SELECT Days As Entitlement
FROM tContractLeave
WHERE ContractTypeID = <variable>
   AND LeaveTypeID = <variable>

And how do you proceed? I don't know. How is this data going to be consumed in your system? Presumably an employee will apply for a leave. Is this system designed to tell them what that leave will be? Will the system do math from existing recorded leaves to calculate what leaves remain? If an employee does not use a leave they are entitled to, do they lose that leave?

What we made is a stand-alone definition of Contract/Leave/Entitlement dynamics. I can imagine a variety of ways this might be consumed, but that is a matter for you to describe in respect to your purpose.

One thing for sure: the actual leaves taken over time by an employee are not handled at all by what we did here. What we made is like a catalog or a truth-table or a selection list. Actual leaves taken should be recorded separately, dated, related to an employee (from which the ContractType is drawn) and where the LeaveType is recorded. Then, you can use that data to look up what the entitlement is.

Makes sense?
Mark

TriciaLatchman · Aug 22, 2017

Hi Mark,

Yes an employee will apply for a leave.

Then I should create another table called tblLeaveApply with
LaLeaveID
LaEmployeeID
LaDaysEntitled
LaDaysUsed
DaysLeft: Nz([LaDaysEntitled],0)-Nz([LaDaysUsed],0)

Is this system designed to tell them what that leave will be? Yes, it should

Will the system do math from existing recorded leaves to calculate what leaves remain? Yes, it should

If an employee does not use a leave they are entitled to, do they lose that leave? Yes they lose all, at the Contract_End_Date.

MarkK · Aug 22, 2017

Yes, so then it is a matter of how you want to handle that. If the application for leave, for instance, is data you want to track, then you need a table for that. It's possible you could do an accurate implementation with a table called EmployeeLeave which has a Status field, and that status might start off as "AppliedFor", and then maybe that changes to "Denied" or "Approved" over time. Maybe even those status change events are recorded in a child table EmployeeLeaveStatus. When calculating leave taken to date, be careful to only sum the EmployeeLeave records where Status = "Approved" and Date <= Today's date, and so on.

Also, a little thing, but intersting: I would never store all of these fields...

LaDaysEntitled
LaDaysUsed
DaysLeft

DaysLeft is, by definition, always a calculation of the other two "hard data" fields, so never store it, always calculate it on demand. Imagine a query like...

Code:

SELECT EmployeeLeaveID, EmployeeID, DaysEntitled, DaysUsed, DaysEntitled - DaysUsed As [COLOR="Blue"]DaysLeft[/COLOR]
FROM tEmplyeeLeave

If you store the result of a calculation of your raw data, you introduce a dependency that could leave you with egg on your face as a developer; you make it possible for your data to be in conflict with itself. As a pro, always make this impossible by never storing the result of a calculation. Only store raw data. Calculate results only at retrieval time and you completely eliminate errors internal to your own data.
hth
Mark

TriciaLatchman · Aug 23, 2017

Hi Mark, understood....but i am still not fully clear on what should be done.

So here's my scenario, Employee apply for leaves using a manual form (Physical Paper) which has a two-fold approval system. The form is is firstly approved by their immediate supervisor and then the HR Manager. The form is then filed. So my aim is to use this database to really track the amount of Days each employee is entitled to, has used and has left within their contract term. I am not sure if I'm explaining myself clearly. Currently we use excel to do this where we only record when a leave as taken when approved.

See attached.
View attachment Leave Report.xlsx

TriciaLatchman · Aug 23, 2017

hi Mark, your comments are greatly appreciated

MarkK · Aug 23, 2017

I think you need an EmployeeLeave table to store actual leave data. I would guess that you can use the same table for future leave as you use for past leave, but future leave may have a different status from past leave. Future leave may be leave that is only "AppliedFor."

But I feel like I've described this already.

Where are you stuck? The process as I understand it is

Employee applies for leave
Super approves
HR approves
Document if filed

What is the workflow? Who does the data entry and when? Employee fills out a form and hands it to super? Super creates the EmployeeLeave record in the system with status of "AppliedFor"? HR changes EmployeeLeave record status to either "Approved" or "Denied", or changes the date and pushes it back to super for re-approval by employee?

You need to study and understand the inter-office dynamics of this workflow, and design the system to do that job. And this is the nitty-gritty part of doing development. Data structures are not that hard. What's harder is understanding who needs to touch the process when, and then write an interface that works for the needs of those users.

So. Create an EmployeeLeave table that has start date and end date of the leave they are applying for, leavetype, status, applied date, what else???

Then, figure out who creates the data in the system, and write them an interface for that job.

And I doubt, once you have that kind of stuff done, that you need a report like this excel report you posted. I would just create a little dashboard maybe per employee that could do those leave calculations, but the nice thing once you are computerized, is you don't need these big excel reports that show everything for everyone. You can just navigate to the employee record, click the leave button, open the leave interface you are working on now and show the data for that person only.

So I hope you aren't going to re-create that Excel thing in Access. In Access you can drill right down to what you need at the click of a button. No need to create big sprawling views that show everything about everyone.

Makes sense?
Mark

TriciaLatchman · Sep 22, 2017

View attachment Employee Database.zip

Can someone please help me with figuring out what's wrong with the attached database. I can't seem to get the report "Entitlements and Balances" to pull the correct info for the Days Taken for the relative leave type.

Please help!

Thanks.

MarkK · Sep 22, 2017

The recordsource of the report depends on the table Employees, but there are no records in that table. As a result, the record source of the report returns no records. To solve this problem, either add rows to the Employees table that will link to your other data, or remove the Employees table from the record source of the report.

Also, as an aside, there are some problems with the tables, for instance, I don't see a way to link a LeaveApplication to an Employee. I think you are not really ready for building forms and reports yet, since the data structures do not look adequate to solve the problem.

hth
Mark

TriciaLatchman · Sep 22, 2017

I understand, i am in the learning process that why i'm asking fo help to build. If i see the steps of examples i will be better able to do accordingly. Please asssit.

MarkK · Sep 23, 2017

I would expect to see tables more along these lines for this problem...
hth
Mark

Annual Leave Database (1 Viewer)

Registered User.

bit cruncher

..forever waiting... waiting for jellybean!

Registered User.

bit cruncher

Registered User.

bit cruncher

bit cruncher

Registered User.

Registered User.

bit cruncher

Registered User.

bit cruncher

Registered User.

Registered User.

bit cruncher

Registered User.

bit cruncher

Registered User.

bit cruncher

Attachments

Similar threads

Users who are viewing this thread