Record Sick Leave via access

[Nixx1401]

I am trying to create a database to record employee sick leave and to indicate when an employee has surpass they alloted sick leave yearly.



I have the following tables and attributes

Employee Table
ID#, FName, MInitial, LName

Status Table
Category
Allocation

Transaction Table
EntryDate
StartDate
EndDate
TotalDays
Certify?
DaysBalance

Category indicates if a person is Temporary, Appointed or Substitute and Allocation indicates how many sick days is allocated per category yearly
I place both in the same table so when the status is selected the allocation is autofill

I am thinking of creating a form with the Employee Table info at top and a subform with the status and the transaction information.

Problem: I would like when a new year begins the sick days not used is discarded and a new allocation of days per employee is generated also when an employees status is change how do you suggest to deal with that. When an employee used all of there sick days is there a way to have all those names display when you open the database.

[jzwp11]

Since your ID# has meaning to your users, it is probably not a good idea to have it as the primary key in your table. It is generally recommended that the primary key have no significance to the user although there is a lot of debate on that topic. I generally use autonumber primary keys in all my tables with matching foreign key fields in related table. The ID# field is a text field and most relational databases are more efficient with number fields especially long integer numbers. Also, it is generally recommended to not have spaces or special characters (#) in your table or field names. In fact, the # sign has significance in Access because it is used to delineate date values.

As to the strbalance and totalday fields in your table, since these are calculated values they are generally not stored but calculated on the fly when you need them.

If you want to designate the status of a person you would add a foreign key field that relates to your status table.


I also saw your other post regarding the datediff() function problems. I got an active X error when trying to input a date in your subform, so I'm not sure what the issue is there. I had to rebuild the database and the forms in order to get the datediff() function to work properly.

Regarding the datediff() function, let's say that someone is sick for 1 day, so the start and end date would be the same, so the datediff() function will return 0. You actually need to add 1 to the result of the datediff() function.

I have incorporated the changes I discussed above. The new database is attached.

[Nixx1401]

thx i understand what you are saying. I would i deal with the expired days yearly and allocation of days yearly and when a person status changes because when you are appointed you are allocated 30 days yearly, temporary 14days

[jzwp11]

If a person can have many statuses over time that describes a one-to-many relationship which requires a different table structure. Instead of having the status in tblOfficerInfo we need to separate it out.

tblOfficerStatus
-pkOfficerStatusID primary key, autonumber
-fkOfficerID foreign key to tblOfficerInfo
-fkStatusID foreign key to tblStatus
-dteEffective (effective date of the status)

Is the allocated time of prorated if the person has the status for a portion of a year? If so, that would have to calculated and applied.

In terms of going from one year to another, you would alter the query I used to limit the calculations to the current year. There are a number of date related functions built into Access that can help filter the data to what you need.

[Nixx1401]

Since i created the status table when i try to create the form using the wizard to have the data from the officerInfo tbl, transaction tbl and status tbl I am unable to have the transaction part as a datasheet it is presenting all the data columnar.

Would i be able to display the allocted time per year, sick leave used, status of officer and remaining days on the form or should I do this by creating a query and generating a report.

[jzwp11]

...officerInfo tbl, transaction tbl and status tbl I am unable to have the transaction part as a datasheet it is presenting all the data columnar.

To which status table are your referring? tblOfficerStatus or tblStatus?

You would not have a subform based on tblStatus on your officer form. You would use a subform based on tblOfficerStatus.

You would then have 2 subforms on your main form. You can change the default view of the subforms to datasheet (via the property sheet-->Format)

[Nixx1401]

I have both subforms on main form and is running nicely there is one challenge I created a query selecting all appointed staff with a data parameter so i could generate a report to display total sick days taken and create a text box to display days remaining and another to to input the amount of days allocated per status to make the necessary calculation. On completion I realise that if a person inputs a time period for 2 years it will reveal the incorrect days remaining for sick leave. How do you think i should approach this.

I was looking at the text box you created on the form (remaining) but its properties dont reveal how the data is generated.

I would have like to have the days taken, days remaining on the main form displayed in red is this possible rather than a report taking in consideration the years and the amount of days allocated per yearl.

Is there a way in which every year i append 30 or 14 days to all staff according to their status. I would create another table allocation table with the following attributes leave allocation ID, allocation for year, Year , and officerID and then create a form to input the necessary data but rather than having the user input 300 records yearly can they be appended according to the status.

[jzwp11]

I was looking at the text box you created on the form (remaining) but its properties dont reveal how the data is generated.

I used Visual Basic for Application (VBA) code in the On Current event of the main form. This is that code:

If Me.NewRecord = True Then
    Me.remainbalance.Visible = False
Else
    Me.remainbalance.Visible = True
    Me.remainbalance = CLng(Me.cboStatus.Column(2)) - DSum("daysoff", "qryDaysTaken", "fkOfficerID=" & Me.pkOfficerID)
    
End If

I used the query qryDaysTaken in conjunction with the DSum() function and the officer ID & combo box (the allocation column) from the main form.

I would have like to have the days taken, days remaining on the main form displayed in red is this possible rather than a report taking in consideration the years and the amount of days allocated per yearl.

This should be possible with some VBA code. Would you just want to show the current year info or for all years? If for all years, you would actually use a subform but then it may be more difficult to do the color coding you wanted.

On completion I realise that if a person inputs a time period for 2 years it will reveal the incorrect days remaining for sick leave. How do you think i should approach this.

You can check the values inputted to make sure the range is only within a 1 year period, if not, reject the values and have the user reenter the values. If you will only want data for a particular year, then just present the user with a choice of years and then you control the actual beginning and ending dates to just that year.

[Nixx1401]

I would really like to have on my main form the data that is pertaining to the current year when the user attempts to enter the data. I have attached the database so you can see how far i have gotten. In the datasheet in the main form i would like the above to be displayed. I am not too good when it comes to writing code.

[jzwp11]

Your table structure still was not correct. I have corrected it, but in so doing, it rendered the forms you created useless that is why having the correct table structure first is so important. I have attached the corrected database with new forms, you will need to put in effective dates for each officer's status (tab on frmOfficerInfo) and the alloted allocation for each status (frmStatus)

[Nixx1401]

Thx for correcting my table structure but how will I be able to view the days remaining from the each year on the main form. hope im not bothering u

[jzwp11]

Thx for correcting my table structure but how will I be able to view the days remaining from the each year on the main form. hope im not bothering u

That still remains to be done, you will need to add the data I requested and repost the database. It is always better to have some data to make sure the VBA code is working.

BTW, how do you determine the # of days allotted if a person changes status in the middle of the year? Does it get prorated?

[Nixx1401]

[I have entered the alloted sick days for each status and have reattached. A person allocation is determined by their status.

[jzwp11]

As I was thinking about your database, I realized that it was not as straightforward as I initially thought because if the time off spanned across a year change, the calculation for the current year's days off would not work correctly. It took a little more effort, but I believe the attached DB should be what you were after.

Since you did not answer my earlier question about how you handle the change in allocation when a person's status changes during the year, I did not figure that in.

[Nixx1401]

thank you very much but i had indicated that a persons allocation is determined by their status if they are appointed gets 21 days or temporary 14 days. Question how did you get the pk officerID and TransID and fkOfficerID not to display in the main form