Vacation Log

N

naja

I would like to create a vacation log for my department. This is what I'm
invision to be like:
Name:
Date:
Available time off:
Date taken:
Balance: (here is where I need the help, creating the right formula)
Approve by Manager:
 
R

rox

Well, you'd need a field that held accrued time (the total vacation time in
days that each employeed had) stored somewhere. Then you'd do this math


= [Total Vacation Time in Days] - [Vacation Time Already Taken]

Do having said that, I may not have understood your question because that
seems kinda elementary. Am I missing what you're really asking?
 
N

naja

Thank you for your reply, but the problem is that I have to keep deducting
from the previous balance. ex: if james accumulated 25 days for the year and
yesterday tood a day off, his new balance should be 24, but then a month from
now, he takes again another vacation day, so now his balance should be 23. I
hope this help. I know that this formula requires some IIF clause, but I'm
not sure how to do it.

rox said:
Well, you'd need a field that held accrued time (the total vacation time in
days that each employeed had) stored somewhere. Then you'd do this math


= [Total Vacation Time in Days] - [Vacation Time Already Taken]

Do having said that, I may not have understood your question because that
seems kinda elementary. Am I missing what you're really asking?


naja said:
I would like to create a vacation log for my department. This is what I'm
invision to be like:
Name:
Date:
Available time off:
Date taken:
Balance: (here is where I need the help, creating the right formula)
Approve by Manager:
Click to expand...
Click to expand...
 
J

John W. Vinson

Thank you for your reply, but the problem is that I have to keep deducting
from the previous balance. ex: if james accumulated 25 days for the year and
yesterday tood a day off, his new balance should be 24, but then a month from
now, he takes again another vacation day, so now his balance should be 23. I
hope this help. I know that this formula requires some IIF clause, but I'm
not sure how to do it.
Click to expand...

One solution is to not store the new balance at all, anywhere!

You have the initial time-off allotment.
You can use a Query to calculate the sum of the time taken off.
Subtract the latter from the former; that's the new balance.

There is no need, and no benefit, to storing it.

John W. Vinson [MVP]
 
N

naja

Thank you for the reply, the only problem is that i need a run down of how
those days were taken on each line of a report so that when I presented is
clear to the manager.

here is how excel formula looks like:

=IF(AND(ISBLANK(D3),ISBLANK(E3)),"",F2-D3+E3)
=IF(AND(ISBLANK(D4),ISBLANK(E4)),"",F3-D4+E4) and so forth.

I'm not sure on how to do this on Access.
 
R

rox

Conceptually Access isn't the same animal as Excel, so be careful not to
rely on the kind of thinking that goes into designing Excel formulas.

You could have a simple table with Total Vacation Time in one field and
Total Time Taken in another...and as the months go by you can just have
whoever is logging the time taken off increase the number that's stored in
"Total Time Taken". So in March the total time taken off would be 1 day, in
April you'd just type over the 1 and make it a 2.

There are more elegant ways to do this, like having a separate table log all
days off by employee and linking that with a query to the table of all
employees and their total accrued vacation--which would allow for more
interesting reporting. That is the way I would do it, if it were my project.
 
N

naja

Thank you rox, it does make a lot of sense what you said, i will try to apply
your idea. once again thanks.
 
Top