Employee Vacation time tracking (1 Viewer)

buratti · Jul 29, 2009

I have an Employee information form with a time off sub form. The sub form has the fields Date, Reason (vacation, Sick, Personal, or Other), and notes. I need a "vacation days left" field in the main form that calculates the total days off for each category and subtracts it from the allowed for that category.
Basically I need to translate this literal verbal equation into a workable Access function: "Count all entries where reason equals vacation (or sick, etc.) and subtract from total allowed". That seems simple enough, but I know very little about built in functions for calculations, but there is also a kicker. I also have a "vacation reset date" field that I need to automatically change to one year later once the previous date has passed. Then I need to filter the sub form to only display records after that date and count only those records (with an option to view all records if necessary) How would I go about doing this, preferably without writing VBA code, which I know even less about?

I will be doing a lot more, that I'm sure I will need help on, with this form once this task is figured out, so if anyone is interested in helping me personally please let me know.

buratti · Jul 29, 2009

I may have already figures out part of it, but cant seem to get it to work correctly. I'm using the DCount() function, but I cant get the right output.
The function written is:
=DCount("[ID]","Employee time off","[Employee ID]=" & [ID] And "[Reason] = 'vacation" ).
That counts ALL records in the [Employee time off] table, as if there were no criteria at all. If I remove the {And "[Reason] = 'vacation" } it counts only the entries related to the current employee on the main form. And vice-versa, if I remove the{"[Employee ID]=" & [ID]}, it will count all the records containing "vacation", regardless of employee id. But when I put them together it counts all records as if that whole criteria was not even there. Any suggestions? What am I doing wrong?

Scooterbug · Jul 29, 2009

Should be:

=DCount("[ID]","Employee time off","[Employee ID]=" & me.[ID] & "And [Reason] = 'vacation'" ).

Personally, I would create a lookup table for Reason. Each reason would have an ID number associated with it. By doing it this way, you can limit what can be used as a reason for missed days. It also eliminates missing an entry due to a spelling mistake if the reason is manually entered with text.

Scooterbug · Jul 29, 2009

Oops..my bad....use to doing things in VBA

buratti · Jul 30, 2009

Thanks, it works great now. Silly how something as simple as a wrongly places quotation mark can mess up the whole function. Its working but just wondering if this is the "right" way to calculate what I need?

Scooterbug said:
Personally, I would create a lookup table for Reason. Each reason would have an ID number associated with it. By doing it this way, you can limit what can be used as a reason for missed days. It also eliminates missing an entry due to a spelling mistake if the reason is manually entered with text.

Sorry, I didn't mention that I do use a lookup table for the [reason] field. Just didn't want to make my question more confusing by doing so.

Now does anyone have a suggestion about how to automatically change the [vacation reset date] field like I stated in the second part of my first post?

Scooterbug · Jul 30, 2009

I dont think that the automatic changing of the reset date is going to be accomplished without VBA. I have a few ideas, but would need to see the db in order to see if they would work. Any chance of posting the db?

You can strip out any data if that is a concern. Also, if you are using Acess 2007, copying it and saving it as Acess 2000-2003 will generally get you more input as not everybody has A2007

buratti · Jul 30, 2009

Scooterbug said:
I dont think that the automatic changing of the reset date is going to be accomplished without VBA. I have a few ideas, but would need to see the db in order to see if they would work. Any chance of posting the db?

You can strip out any data if that is a concern. Also, if you are using Acess 2007, copying it and saving it as Acess 2000-2003 will generally get you more input as not everybody has A2007

Yeah, I'll copy and clean out other junk and post it later today, just one question... HOW? Just as an attachment? Also, Im pretty sure its access 2000-2003 format, but I am using Access 2007 though. Dont mind the sloppyness of it either... I just want to get all my functions and calculations working before I visually clean it up. I have a lot more additions/tweaks I want to add also, so if you would be willing to assist me in those additions please let me know. And thanks for your help already.

Scooterbug · Jul 30, 2009

The best way is, once it's cleaned out...
1. Compact and Repair the Database
2. Zip it up
3. When you click "Post Reply", there is a paperclip icon. Click on that, it will open up a new window. Click the Browse button, naviagate to the db and select it.

As for helping...will do what I can.

buratti · Jul 30, 2009

Ok here's the databse. The vacation reset date is essentially one year incriments from the start date if that helps you any. Also how would I count only the records between last years reset date and this years? The formula you figured out that works would end up counting all records in the table

Scooterbug · Jul 31, 2009

Alrighty, got it working. I know you had wanted to do it without VBA code, but unfortunately I couldn't do it. I've attached the db with the changes.

In a nutshell....

I created a module with a function for finding the vacation reset date. I added comments to hopefully explain what was going on in the code. Take a look at it to see what is going on. If you have any questions, feel free to ask.
For the remaining vacation days, I simply added another criteria to the control source. I told it to grab the days that were between the vaction reset date - 365 and the vacation reset date. That should do the trick.

Also, try not to use spaces in table names, field names, etc. That forces you to manually add brackets when referring to them. Forget to add a bracket and you will get errors. Also, using special characters should also be avoided. I noticed that you had a form name with parenthesis in it. That can also cause problems.

Feel free to ask for help if you get stuck again. Good luck!

buratti · Aug 1, 2009

Oh thanks so much. I looked at it briefly last night, but didn't really get into it yet, been busy, but I trust that if you say it works then it must. I'm just going to visually tweak it a little bit then it’s on to my next problem I can’t seem to figure out. I'd be so grateful if you'd be willing to assist me on my next task also.

I need to restructure the employee worksheet table/form. As you may see right now it’s just a single form created from a single table, in no way linked to the employee table. The end result I would like for the appearance of the form to be similar to what it is now, but have the data linked to the employee table. I started to redesign it by creating the Employee log table. I plan on adding another option group, w/an employee log subform, to the employee form. My problems I can’t figure out are as follows:
First of all, right now the pay rate is hard coded into the formulas. If/when that changes I have to find and change it in each of the formulas it is used in. I would like for the total pay for day formula take the pay rate directly from the employee table instead. (That may be simple enough where I can figure it out myself)
The main thing I can’t figure out is that I would like a separate form visually similar to the now Employee Worksheet form (which will be replaced), meaning that all the "active" employees are across the top columns, and the work days are the rows going down with amount paid for day formulas taking the calculation data from the employee table.
By "active" employees I mean that I will add an Active checkbox to the employee table and have the new form only display employees that have that box checked.

Would this format of a form even be possible, and can you understand what I am trying to get at? I posted a few threads about this already, but seem to have a hard time to put into words what I need. If you can help I understand. You have done enough already. Thanks

Scooterbug · Aug 3, 2009

The problem I see with the Employee worksheet, is that you have fields associated with each employee. What happens if you add an employee or fire an employee? You would have to go in and add or remove fields. Not a good database design.

Personally, to figure out and display wages, I would use a report. You can then display the data in several ways. As far as displaying it the way you have it now (like a calander), off the top of my head I dont know how I would display it that way. But you could group by date and display the employee and their wages.

You are going to also have to have another table to record the date and hours worked. Something like this:

tblHoursWorked
pkHoursWorkedID Primary key
EmployeeID Foreign Key
DateWorked Date worked
HoursWorked Hours worked
PayRate Hourly Wage

The pay rate can be pulled from the employee table for when the row is entered in.

buratti · Aug 3, 2009

Scooterbug said:
The problem I see with the Employee worksheet, is that you have fields associated with each employee. What happens if you add an employee or fire an employee? You would have to go in and add or remove fields. Not a good database design.

Thats exactly my problem now and why I want to redesign it. When I first created it that was the only way I could figure out how to display it in the mannor I wanted. I was working on it over the weekend and added another subform to the employees form from an employee log table that displays the "log" for that employee. I also figured out the formulas and such to calculate pay.

The only reason that I wanted the, as you called, the calander format is because when I'm entering the daily work and hours, I do it all at once, and it is easier to do it on one screen - tabbing over to the next employee, as opposed to finding each employee record, entering the data, then finding/navigating to the next and so on.

I have never really needed to create a report im my uses in Access, so I dont know much on how to create them. I'm hoping that a report CAN display the data in the format I need due to a weekly printout I do of the data. I will work on it a little bit and see if I can come up with anything myself, but If I can't I'll let you know and hopefully you can help.

On a different note... You mentioned that its bad to have spaces in table and field names. If I go ahead and rename those to a better format, will all the reference to them in formulas, relationships, VBA code, etc be updated automatically, or do I have to search and replace each one individually?

Scooterbug · Aug 3, 2009

buratti said:
The only reason that I wanted the, as you called, the calander format is because when I'm entering the daily work and hours, I do it all at once, and it is easier to do it on one screen - tabbing over to the next employee, as opposed to finding each employee record, entering the data, then finding/navigating to the next and so on.

A form can be created so that it shows all the Employees and fields to put in the Time they start work and the time the end work.

I have never really needed to create a report im my uses in Access, so I dont know much on how to create them. I'm hoping that a report CAN display the data in the format I need due to a weekly printout I do of the data. I will work on it a little bit and see if I can come up with anything myself, but If I can't I'll let you know and hopefully you can help.

I'm sure that a report can be generated to your liking and format. Anything you can display in a form can be displayed in a report without a problem.

If you want to post an updated version of your database, I will be happy to take a look at what you have got so far.

On a different note... You mentioned that its bad to have spaces in table and field names. If I go ahead and rename those to a better format, will all the reference to them in formulas, relationships, VBA code, etc be updated automatically, or do I have to search and replace each one individually?

Bob linked to a tool to find and replace. As he said, fixing them now will save you a lot of heartache in the future

rhinnon · Feb 2, 2010

This thread is FABULOUSly helpful. I am having to make a database for something much simpler than described here but the basics are still here.

I did however have one question that I have as yet to find the answer to.
I will be maintaining this database bi-monthly and will need to produce a report that I can send to all of the managers and supervisors that will detail the available leave time per employee. I will need to show the employees hire date, full name, employee # and the available time. Since this information is not put into a table (based on this test/sample db) how would I produce such a report?

Your help is greatly appreciated.

pearls9 · Feb 16, 2010

Hi,

I am an HR Manager for a manufacturing company in Kentucky and am currently tracking employee attendance manually in three different programs. We use a program with a scanner to record employee's time worked and where I input vacation days and other time-off manually. Then I put that same information into an Excel workbook (each employee has their own worksheet) that tracks vacation time available and used and occurrences they have received for unauthorized time off. And then I key total hours from the scanner program into a payroll system to generate the employee’s paychecks.

I am very, VERY impressed with the Employee Attendance database on this thread and would love to use it for my company. I am in the process of learning Access (self-taught) but am not far enough along to reconstruct some of the particulars that would need to be changed to fit the specifics of my attendance policy. (I just can’t seem to find the time…maybe it’s because I have three programs to maintain!!!)

I recently found that I have the capability to download information from the scanner program to an Excel spreadsheet. I have been able to import that information into the payroll software which saves one step....now I believe that with some minor changes to this Employee Attendance Database, I would be able import the same information to track the vacation/occurrence portions. You cannot even imagine how much time this would save me!!

Would someone please help me with this?

I have done a lot of research and found different databases that contain some of the things I would like to add…but I don’t know how to incorporate them or utilize them in this database.

This is what I need help with:

1) Calculating accrued vacation (I believe that the available vacation in this database is keyed in manually.) Our vacation is based on hire date and can be carried over from year to year. Vacation time is earned: 6 months to 1 year – 5 days, 1 year to 10 years – 10 days, over 10 years - 15 days. (Managers get 15 days automatically on date of hire.) Vacation can be taken by full or ½ days. (I would like to take the scanner programs download (excel) of the days taken from and just import that data into the database on a weekly basis for the current vacation available.)
2) Calculating occurrences. Each employee begins with a “bank” of six points. If they miss for an unexcused reason (full or ½ day) they receive either a ½ or full occurrence which deducts either ½ or full point from their bank. They must then go 30 days without getting an occurrence to earn ½ point back. So, if an employee has one full occurrence, it would take them 60 days without an occurrence to earn that full point back. I am able to code the occurrences in the scanner program and download, but if I could get the database to do the calculation for earning the points back for a current running total– that would be AWESOME!
3) I will not use the “Personal,” “Excused” or the “6 month attendance” portions of the database so those things can be eliminated.
I hope my requests are not too cumbersome! Any and all help would be greatly appreciated.

marinx · Feb 7, 2015

HI.

Based on the testing database.mdb that you attach in that thread how its possible to to count numbers of days (for example 5) . Now is count only [date off field] that is only for one day. I will create and put manually the StartDate and EndDate and the i will manually calculate the Days of vacation as TotalDates .

Thanks in Advance

PaulD2019 · Dec 1, 2019

Sorry for bumping an old thread but the vacation tracker part of the testing database in post 10 by Scooterbug should help me with something I have been asked to create.

One thing I noticed when testing it is that the subform for vacation days runs showing all of the staff members days off even when the holiday period is changed for example changing the holiday period start date from 02/04/2019 to 02.04.2020 to add the next years holidays, sick days, personal days etc.

Is there a way to filter the subform so it only shows dates that fall from the period start date to the period end date on the main form?

Gasman · Dec 1, 2019

Try the attached.

FYI I added criteria for the dates to the subform query using the main form start and reset dates.
Then in the AfterUpdate of the start date, requried the subform.

HTH

jdraw · Dec 1, 2019

PaulD2019,

I recommend that you create a new post. You can reference this thread and the database if you wish, but it might get more attention/responses if you describe your requirement in a little more detail with a few examples.

Good luck.

Employee Vacation time tracking (1 Viewer)

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

New member

New member

New member

Registered User.

Enthusiastic Amateur

Attachments

Super Moderator

Similar threads

Users who are viewing this thread