Calculating values to fill a table

[PlumDingo]

I have a database that maintains the vacation for the bargaining people at work. Currently, I have a form where you are required top manually enter the person's earned vacation hours, carry over hours and then the hours remaining from the currently scheduled hours, Is there a way to automatically fill in that information. A person is granted vacation based on their seniority date.

An example of how vacation is granted:

Person hired Jun 13, 2003
On Jan 1 2004, person has 10 days plus one day for every month incl June since hired prior to the 15th. (total 16 days)

Person hired December 16, 2003.
On Jan 1, 2004, person has 10 days vacation.

Person hired Jan 1, 2004.
They will earn 10 days vacation that can be taken after 6 months of probabtion.

I currently have the following tables:
Employees
Job Classification
Vacation Scheduled
Supervisors
Vacation Earned - which is currently empty.

Each table references the employee by an employee ID whether primary or foreign key.

It is kind of tedious to enter this information manually because whenever a prson changes the table has to keep being update (multiple tables). This could be a biweekly process. Just trying to automate what I can.

Thanks!!!

 

[dhookom]

I see a couple examples but probably not the full list of specifications/rules on how vacation is granted. I don't see how anyone can suggest an automated system without a full understanding of all the rules.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PlumDingo]

When i say vacation is granted, I mean how vacation hours are earned by each employee. Granting vacation can never be automated because it looks at negotiations, seniority, weather etc... I think I figured out how to fill an existing table with calculated values bbut I am trying to get when a person enters a new employee on a form that the table will automatically have the number of hours that a person has earned based on their seniority. The clerk would have to enter the number of carryover hours.

Does that make more sense?

 

[dhookom]

enters a new employee on a form"... don't all new employees just get a set amount of days? If the days are pro-rated based on the first day of work, what is the rule for pro-rating?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PlumDingo]

I have not gotten that far yet. From my first post, that is the rule as to how someone is granted vacation. It is dependent on years of service and is given in terms of hours.

I am not sure that I am answering your question, however.
I had to keep that assumption simple. Getting more complicated, a person gains one day of vacation for every month that they work as long as they are hired prior to the fifteenth of the month for a total of 10 days that year. In other words if you are hired in January before the 15th, you will have 10 days or 80 hours of vacation eligible that year in October.

Is that making better sense?

 

[dhookom]

Doesn't this mean a new employee or new hire "I am trying to get when a person enters a new employee on a form that the table will automatically..."? Why are you adding information about months that they work?

It seems you are asking us to provide some functionality but not specifying what you needs.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PlumDingo]

I get your question now. I am sorry for the confusion. An employee might be new to a location but not a new hire. For example, a person could have a hire date of 9/3/02 and just join a location on 4/10/04. This is because vacation (the actual time off) is granted locally by location and there is a local database that is used to keep track of who is on vacation when. This also helps in keeping track of how many days (hours) of vacation is left without having to go through HR.

All new hire employees have to go through a training course first, so even if that person is a "new hire", they would still have time on property with vacation time earned (which they can start taking after 6 months of probation).

Does that make more sense.

 

[dhookom]

I think you understand a bit where I am coming from. However if you expect someone to assist with code or whatever to calculate values, you still need to provide all the logic and calculations.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[willir]

PlumDingo

You can do some of the "work" with your design. You will probably have a table for your bargaining units - depending on the size of your company, you may have more than one unit.

You can setup a complementary table with your "rules".

tblBargainingGroup
BargainingUnitCode - primary key
GroupName
ProbationPeriod - numeric, interger
...etc

Discussion:
The probation period would be the number of months before the employee is eligible for vacation.

tblVacationRules
BargainingUnitCode - foreign key to bargaining table
BargainYear - interger or text for the contract year
MaxYears - numeric, interger
DaysEntitlement - numeric, interger

Primary code - BargainingUnitCode + BargainYear

Discussion:
Max months would be the number of years (or you could do months) for the Entitlement number of days vacation.

Since there may be changes in alloted vacation, either by law or per the agreement, I included the bargaining year.

The way it works...

Has the employee worked beyond the probation period?
If Yes, what is the maximum MaxYears the employee has worked without exceeding MaxYears - grab the DaysEntitlement.

This design allows you to change the "rules" on the table, instead of using code.

Richard

 

[PlumDingo]

I just got this posting. With the new baby, somehow I missed the ntification on my email.

Okay Richard, let me see if I understand what you are saying. First I create a table called Vacation Rules that basically has five rows and each row defines the rules for vacation for the five different earning years. I don't think that I need the table Bargaining group because the probation period is the same for all classifications and the remainder of the information can be found in the table Classification ID that has the following fields:


Classification ID
Job Classification (text description of the ID #)

The table Vacatio Rules would have the following:

Rules Index (autogen) primary key
Max Years which would be something like
<1, 8 hours for every month
4, 80 hours
15, 120 hours
20, 160 hours



How am I doing so far?

Okay then at each table value in the vacations earned table, I would somehow reference the table in the design view to use those conditions.
I tried to use a look-up wizard but that does not work. Am I following on the right track here? I think that my problem is that regardless of classification the probation period and the contract year of the same.


France
Just trying to get by.