pronate
Actually, although the leave type is important to what you want to do, you really want a "transaction" file that tracks leave activity...
tblHistory - track leave activity and leave type
HistoryID - primary key, autonumber
EmployeeID - foreign key to employee table
LeaveType - text field, see comments
DateOut - date field
DateIn - date field
Comments - Memo field - document issues
+ anything else specific to the leave of absence
LeaveType
You may want a table to store the types of leave. Or you can just store the information in a list associated with a combo or list field on the data entry form. Probably best to have a Leave Type field -- a little flexibility, plus can be used to store other information such as payroll codes, unemployement eligibility, max number of days allowed (ie, short term vs long term disability).
tblLeaveType
LeaveCode - primary key, text field, code used for leave, eg STD, MAT, PAT (paternity leave?? - UK or Canada??)
LeaveDescription - text field
Plus other info; consider
- LeaveRestriction
- MaxDays - maximum allowed days
- InsuranceCode - code use by insurance company
An alternative to tblLeaveType
LeaveID - autonumber, primary key
LeaveCode - text, unique
plus the rest...
This variation uses an ID number instead of the code as the primary key. This would allow you to more easily change the leave code without impacting the data. If you use this alternative, then you may need to change the tblHistory...
LeaveCode -> LeaveID
The employee table will be fairly straight forward...
tblEmployee
EmployeeID - autonumber, primary key
LName - last name
FName - first name
SSN / SIN, etc...
You may want to have a check box that indicates if the employee is or is not on leave. Kind of breaks a rule on normalization but makes the screen a little more friendly.
Next question,
How to display the leave activity and find out the current status?
You have a many-to-many realtionship between employee and leave type....
- An employee can go on different types of leave
- Many employees can be on the same leave type
A many-to-many relastionship requires a joining or intermediary table ... but ... your leave history is this table.
Leave history can be displayed in a continuous form; perhaps embed the continuous form as a subform in the employee form.
Current status can be found by finding the most recent date. This can be displayed as a "status" unbound text field on the employee field.
What is not covered...
This solution may not be adequate for absences per a union contract; specifically incidences.
This solution would need to be tweaked if tracking hours as well as days. i.e., track tardiness, suspension, etc.
But after reading Paul Litwin's article, along with this info, you should have some ideas on where you want to go.
Richard
