Seek help/ advice (1 Viewer)

[Moote Tiira]

Hi everyone, I'm new to this group..... and I needed some help or advice creating a leave record management system for my workplace using ms access. Thanks

 

[theDBguy]

Hi. Welcome to AWF!

You came to the right place.

 

[Moote Tiira]

thank you...

 

[Moote Tiira]

Can anyone help with some advice on how will I start? I attached a sample template if you can determine how to create a leave system to get this report.

 

[GinaWhipp]

Welcome to the Forum!

Hmm, perhaps you could us a little background? Like, have you set up any tables yet? How familiar are you with Access?

 

[The_Doc_Man]

Hello and welcome to the forum. You are new here so haven't figured out our ways yet. Do NOT take this as a knock against you, but normally we post problems in a specific topic area. Your problem sounds like a general issue and we DO have a General Help heading.

You do not indicate your level of experience with Access, but your question suggests you are new to the world of Access. Therefore, I am going to assume that you are a novice. If I am wrong, please forgive the incorrect assumption.

I think your best start would be to learn about Database Normalization, which would give you hints about proper layout of data tables and how to best define content for maximum efficiency. Since this is a database forum, you can use our search feature (far right of top-line menu bar) to search this forum of "Normalization." OR you can search the Internet, in which case you should search for "Database Normalization" - because there are other kinds of normalization in math, chemistry, diplomatic relations, ... probably a few others.

IF you search the web, you will find articles from all over the place. I suggest that you start with hits from the .EDU domain - usually colleges and universities - who often have very good summary or short lesson articles. After you have studied a couple of the .EDU sites, look at what the .COM sites have to offer. I directed you towards .EDU first because .COM sites often have something to sell that would be distracting.

After you understand normalization, you would quickly see that you have multiple tables implied by what you presented in your PDF. At that point that you understand WHY this is so, you will be ready to start internal data layout, which is the actual proper starting point for ANY project.

 

[Pat Hartman]

Leave is in essence a transaction oriented process. A person accrues leave at some rate and then spends it. If you calculate leave accrual, you would do it monthly or weekly or whatever your personal manual says. That means running an append query that calculates the time worked since the last "accrual" and appends a new earnings record. Spending leave is always a manual action that is usually recorded after the fact for short term events like a few hours or a day but sometimes before the fact for vacation.

Open Access and look at the templates. I don't recall if there is one for leave. You can also try searching. Take a stab at the tables and we'll help you.

 

[theDBguy]

Can anyone help with some advice on how will I start? I attached a sample template if you can determine how to create a leave system to get this report.

Hi. Perhaps you could also take a look at this sample data model.

Employees Leave Record Keeping Data Model (databaseanswers.org)

 

[jdraw]

Welcome aboard AWF!

 

[Pat Hartman]

I don't like that schema. It keeps a running sum of leave rather than using transactions. The leave table should have individual records that accrue leave or spend it with possibly other types of actions. Then this table is summed to find the available leave. If you update the leave table to add/subtract leave, you loose your audit trail.

PS, much as I love the concept of that site, I have never actually found a schema there that I would use.

 

[Moote Tiira]

Welcome to the Forum!

Hmm, perhaps you could us a little background? Like, have you set up any tables yet? How familiar are you with Access?

Thanks Gina, Yes I have set up tables i.e employeetbl, leavetbl and leavetypetbl
employeetbl contains
StaffName, Home Island and Annual Leave Earning (Earning)

leavetbl contains
Balance brought forward (Bal_BF), Annual Leave Earning (Earning), Eligibility (Total Bal_BF+Earning), Date Leave start (Start_Leave), Resume Date, No of days taken (NumDays), Balance (Bal_BF+Earning)-NumDays
Note: I want the different bwt Start_Leave & Resume Date to exclude weekends (Sat & Sun) and Holidays.

leavetypetbl
Annual leave (AL), Compassionate leave (CL)

Can you please help with some advice for how to set query and forms for this annual leave system.

Thanks agains

 

[GinaWhipp]

I think we need to adjust the tables first.

tblLeave should have a Start Date and End Date with the calculation of days out on leave in a query. If you are looking to exclude Holidays you will need a tblHolidays with the a list of the Holidays *given*. There are also funstions out there for Workdays only, Google those.

 

[Cronk]

The sample form in #4 indicates leave periods being able to be less than a full of a day. Recording Start/End Dates is not enough.

 

[GinaWhipp]

The sample form in #4 indicates leave periods being able to be less than a full of a day. Recording Start/End Dates is not enough.

Hmm, missed that. First thing that comes to mind is Start Time and End Time to be added to the table. Only need to fill in if less than a day.

 

[Pat Hartman]

No table should hold the net leave. This is a calculation and storing a calculated value violates second normal form.

LeaveTbl should be:
LeaveTranID (autonumber PK)
PersonID (FK to Persontbl) (UniqueIDX fld1)
LeaveTypeID (FK to LeaveTypeTbl).
StartDT (UniqueIDX fld2)
EndDT
Hours (Only allowed if StartDT = EndDT. Standard work hours assumed otherwise)

LeaveTypeTbl should be:
LeaveTypeID (autonumber PK)
LeaveTypeCD
MultiplyBy (either +1 or -1, used to allow all hours to be entered as positive values. This indicates which add to hours and which subtract.)

This gives you the information you need to calculate available leave. It also allows you to do any other analysis you want to do regarding leave.