Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Boise, Idaho, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    consecutive months and more (2002)

    I have a form that asks for estimated temporary working hours in one year. I am asking for hours per month and have a space on the form for each month.

    I have to calculate benefits based upon the following criteria:
    If this person works 87 hours or more per month for 5 consecutive months then they are eligible for benefits.
    benefits are $6,493 per year, incrementally paid as $541 during each month worked.
    Temporaries can be hired at any time and can work up to 1385 hours in any one year and can be rehired after a 3 month break in service.

    Here is the easiest example:
    a temporary is hired in January and works full time to July 15. That would be more than 5 months + more than 87 hours per month = benefits.
    calculation is 541 x 7 for 3,787 If the same temporary comes back in September and works 2 more months then calculation for the whole year is 541 x 9 for 4,869. Hopefully that kind of explains how it works.

    I had that all working fine - until someone threw the following wrench -
    Hire the temporary full time from November to March then again in May and again for July and August. That's the maximum time they can work in one 12 month time period. (For this case November to October) Benefits calculate at 8 x 541 for 4,328.
    So how do I find out if there are 5 months in a row when I don't know when the person starts work?
    I'm not an expert at code by any means though I know may way around a bit... I've been able to calculate this fine if everyone starts in January (just go 12 months.....) In this case if I start looking at January and go through December, there are 8 months but not 5 consecutive months. This temp worked November to March which is 5 consecutive months. how do I cross over this imaginary calendar line? (i.e. make the calendar a 12 month period regardless of when it starts?

    I know there has to be a way to accomplish this. Is there someone out there willing to baby me through this problem?
    I apologize for the length and attempt at an explanation. If I can clarify the problem in any way, please let me know...

  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: consecutive months and more (2002)

    Hmmm, interesting issue, but it should be pretty easy to fix. Do you have the data in a table format, if you do, post it, so I can show you how you could do this in a query, with a little function. The best way to do this, is to use DateSerial, and just add/subtract dates. If you post how your table (recording the hours) is setup, I can show you how to do this, there are too many variables otherwise, to post an good example.

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Boise, Idaho, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: consecutive months and more (2002)

    Drew
    thanks for your response.
    I'm not sure what you want me to post - my table fields? or the data in my table?
    What is the best way to show you this information?

  4. #4
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: consecutive months and more (2002)

    Table Fields and data. Preferably a little sample data in an .mdb containing just the sample table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •