MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

LookUp Table

• Follow

```I want to monitor how much vacation time a person in the office has but we
all accrue it at different rates.  A person that has worked for the company
for less than 5 years earns 2.5 hours per pay period, 5-10 earns 3.7 and more
than 10 earns 4.7.  Can I create a “LookUp” table based on a persons Date Of
Hire to determine how much vacation hours to calculate from.
```
 0
Reply Utf 12/23/2007 5:55:01 AM

```There are many factors that would influence the choice of the best approach
for this.

Firstly, you will need a table with the values to look up, e.g.:
MinYears        MinutesPerPayPeriod
0                    150
5                    222
10                    282
(I'm suggesting you store this as minutes to facilite date/time calculations
and avoid rounding errors.)

Presumably your Employee table has a DateOfHire field. You can therefore
calculate the number of years of service, and get the Minutes for that
number of years. The calculation of number of years will be similar to this
calculation of years:
http://allenbrowne.com/func-08.html

You may also need this info to create a query that gives you the MaxYears as
well:
http://allenbrowne.com/ser-58.html

And to get the number of hours into your main query, you will need a
subquery:
http://allenbrowne.com/subquery-01.html
or a DLookup() expression:
http://allenbrowne.com/casu-07.html

Now to put those techniques to good use. Presumably you have a table where
you record the number of hours per employee per pay period, and the pay
rate. So you could add another field to that table to store the number of
hours accrued, and have this calculated and stored as part of that record.
It's not clear here how you handle part timers, e.g. whether their a person
who works half time gets half the number of hours per period.

You will also need to provide a way to record when an employee takes leave,
so you can subtract the number of hours actually taken from the number of
hours due.

There will also be other complicating factors, such as sick leave or
compassion leave which may also need to be stored and accumulated, and
possibly lost after a period if not used without losing the accumulated
holiday leave, provision for long service leave, and handling cases where
accumulated leave may be cancelled (e.g. if the employee is found guilty of
fraud.)

You may need to check out what laws apply where you live, but don't forget
that these laws may change over the years, so you may find there are some
laws applying to leave accumulated before a certain date, and other laws
after that time.

So, there's plenty to think about. :-)

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"open a adobe file from a command button"
<openaadobefilefromacommandbutton@discussions.microsoft.com> wrote in
message news:2B72E02E-50F8-438A-9E54-3A9BB25A9C6F@microsoft.com...
>I want to monitor how much vacation time a person in the office has but we
> all accrue it at different rates.  A person that has worked for the
> company
> for less than 5 years earns 2.5 hours per pay period, 5-10 earns 3.7 and
> more
> than 10 earns 4.7.  Can I create a “LookUp” table based on a persons Date
> Of
> Hire to determine how much vacation hours to calculate from.

```
 0
Reply Allen 12/23/2007 7:18:32 AM

```Thank You.  Most of what you say I somewhat understand, implementing it is
something else, can I hire you to help me with this project??

Thanks

"Allen Browne" wrote:

> There are many factors that would influence the choice of the best approach
> for this.
>
> Firstly, you will need a table with the values to look up, e.g.:
>     MinYears        MinutesPerPayPeriod
>             0                    150
>             5                    222
>           10                    282
> (I'm suggesting you store this as minutes to facilite date/time calculations
> and avoid rounding errors.)
>
> Presumably your Employee table has a DateOfHire field. You can therefore
> calculate the number of years of service, and get the Minutes for that
> number of years. The calculation of number of years will be similar to this
> calculation of years:
>     http://allenbrowne.com/func-08.html
>
> You may also need this info to create a query that gives you the MaxYears as
> well:
>     http://allenbrowne.com/ser-58.html
>
> And to get the number of hours into your main query, you will need a
> subquery:
>     http://allenbrowne.com/subquery-01.html
> or a DLookup() expression:
>     http://allenbrowne.com/casu-07.html
>
> Now to put those techniques to good use. Presumably you have a table where
> you record the number of hours per employee per pay period, and the pay
> rate. So you could add another field to that table to store the number of
> hours accrued, and have this calculated and stored as part of that record.
> It's not clear here how you handle part timers, e.g. whether their a person
> who works half time gets half the number of hours per period.
>
> You will also need to provide a way to record when an employee takes leave,
> so you can subtract the number of hours actually taken from the number of
> hours due.
>
> There will also be other complicating factors, such as sick leave or
> compassion leave which may also need to be stored and accumulated, and
> possibly lost after a period if not used without losing the accumulated
> holiday leave, provision for long service leave, and handling cases where
> accumulated leave may be cancelled (e.g. if the employee is found guilty of
> fraud.)
>
> You may need to check out what laws apply where you live, but don't forget
> that these laws may change over the years, so you may find there are some
> laws applying to leave accumulated before a certain date, and other laws
> after that time.
>
> So, there's plenty to think about. :-)
>
> --
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "open a adobe file from a command button"
> <openaadobefilefromacommandbutton@discussions.microsoft.com> wrote in
> message news:2B72E02E-50F8-438A-9E54-3A9BB25A9C6F@microsoft.com...
> >I want to monitor how much vacation time a person in the office has but we
> > all accrue it at different rates.  A person that has worked for the
> > company
> > for less than 5 years earns 2.5 hours per pay period, 5-10 earns 3.7 and
> > more
> > than 10 earns 4.7.  Can I create a “LookUp” table based on a persons Date
> > Of
> > Hire to determine how much vacation hours to calculate from.
>
>
```
 0
Reply Utf 12/23/2007 4:08:00 PM

```Thanks for the offer, but we are fully booked up for the next 6 months.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"open a adobe file from a command button"
<openaadobefilefromacommandbutton@discussions.microsoft.com> wrote in
message news:857D3F83-5F8B-4EF9-B27E-F8EA01EBC8DF@microsoft.com...
> Thank You.  Most of what you say I somewhat understand, implementing it is
> something else, can I hire you to help me with this project??

```
 0
Reply Allen 12/24/2007 1:09:56 AM

3 Replies
199 Views

(page loaded in 0.133 seconds)

Similiar Articles:

7/22/2012 3:58:26 AM

 Reply: