Calculating Vacation Time

[Guest]

Hello, I am creating what I hope will be a very simple database. It has two
tables; one with employee personal information, one with their vacation time.
In the vacation time table, I have a number field that lists the total
amount of time each person is entitled to, another field formatted as a date
to list the date that they take off, and a third field that lists the type of
vacation. We bunch everything together; sick, holiday, personal etc. If the
type of vacation day is "carryover" it should not be subtracted from the
total allotment. I really just want to subtract the day from the total as
long as the day is not a "carryover", but these are not all formatted as
numbers. I then want to group them by employee. I'm still too new to
programming in Access to have a handle on how to do this. Many thanks in
advance for any help.

 

[Smartin]

Hello, I am creating what I hope will be a very simple database. It has two
tables; one with employee personal information, one with their vacation time.
In the vacation time table, I have a number field that lists the total
amount of time each person is entitled to, another field formatted as a date
to list the date that they take off, and a third field that lists the type of
vacation. We bunch everything together; sick, holiday, personal etc. If the
type of vacation day is "carryover" it should not be subtracted from the
total allotment. I really just want to subtract the day from the total as
long as the day is not a "carryover", but these are not all formatted as
numbers. I then want to group them by employee. I'm still too new to
programming in Access to have a handle on how to do this. Many thanks in
advance for any help.

I would rethink the vacation time table.

If I understand everything about your set up correctly, the "total
amount of time each person is entitled to" is a calculated value, which
is usually a bad idea to store in a table. You can calculate it if
everything is set up properly, and it will always be accurate (assuming
your data entry is also accurate).

I would add a column to indicate how much time is involved per
"vacation". Suppose an employee takes two hours off as "personal" in the
morning and one more as "sick" in the afternoon? I know you said you
lump it all together, but it is easy to make this distinction now, but
if you change your mind or business rules later it will be hard if you
do not make distinct entries for each event.

Also add a column, a foreign key, that identifies the employee from the
employees table. It's also a good idea to add a primary key column,
which can be an autonumber field.

To get things rolling you would add a credit for each employee to give
them a starting point, then add one record per employee per vacation to
debit them. Use positive and negative numbers to represent credits and
debits in whichever sense works best for you.

To do the data entry you would create a form with a subform. The parent
form is bound to the employees table while the child (subform) is bound
to the vacation time table.

To find out how much time a person has in their bank you would create a
query which joins the two tables on the employee id, sum the vacation
debits and credits, grouped by employee, limiting the set using a WHERE
clause to exclude "carryover" entries.

None of this requires programming, just some familiarity with the Access
interface. All the forms and queries can be built using drag and drop
tools. Let us know if you need help with anything!

 

[Smartin]

Well, a date in Access actually is a number, just not the kind you want
to be adding and subtracting (^:

One approach that might work is create a totals query on your vacation
time table. Assuming you only create a record when vacation time is
booked you would want to find a count of records grouped by employee id.
It might look like this in the query builder:

Field: EmpID DaysOff VacaType
Table: VacaTime VacaTime VacaTime
Total: Group By Count Where
Sort:
Show: check check no
Criteria: <> "carryover"

Note it doesn't really matter which field you count. I just used
"DaysOff" for clarity.