Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating the answer (2000)

    Explaining this is going to be as hard as solving it.

    In a query of a few tables I have the following fields related to the problem to be solved...

    HC: Sum([#Employ]+[#Temps])
    Avail Hrs: ([HC]*160)
    Tot Labor Hrs: ([Rfnd LaborHours]+[BP LaborHours]+[Acc LaborHours]+[NetCredApp LaborHours]+[Upgrd LaborHours]+[GA LaborHours])
    Eff: ([tot labor hrs]/[avail hrs])


    The [Eff] calculates to be the Effiency Percent. The higher the number, the harder everyone is working (no one want to work too hard!) I need to add a variable field for [ReqAdditionalHeads] that will increment itself by 0.5 until I have a number, that when combined with [HC], will give me an [Eff] as close to 70% without going under.

    I was thinking about Vb to add 0.5 in a loop to the [ReqAdditionalHeads] until the [Eff] is not below 70% but this is too complex a problem for someone who completed the Vb Basic class THIS WEEK!
    (A little knowledge has gotten me in too deep!)
    There is always a way.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating the answer (2000)

    This is more of a maths problem then a programming problem. If I have understood your explanation correctly, it's possible to work out the answer on paper, no need to get all loopy. Some browsers don't display the symbols for greater than or equal and less than or equal correctly, so I will attach the maths part as a text file. The solution is<pre>ReqAdditionalHeads : Int(([Tot Labor Hrs]/112 - [HC])/0.5)*0.5</pre>

    Please study the text file to see how I arrived at the answer.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating the answer (2000)

    This is outstanding.

    Perfect solution.

    I can not imagine how I can pay you back for this one but you bailed me out big time!
    Thanks!!!
    There is always a way.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating the answer (2000)

    How do I add the condition to change any answer that is less than 0.5 to be zero?

    I am seeing now that there are times when the Eff was less than 70% and it is returning a negative.
    There is always a way.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating the answer (2000)

    Please forgive me for the STUPID Question...

    I got it in... "iif class 101"

    ReqAdditionalHeads : IIf((Int(([Tot Labor Hrs]/112-[HC])/0.5)*0.5)<0.5,0,(Int(([Tot Labor Hrs]/112-[HC])/0.5)*0.5))
    There is always a way.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating the answer (2000)

    Good you found it yourself!

    Or, slightly simpler:

    IIf(([Tot Labor Hrs]/112-[HC])<0,0,Int(([Tot Labor Hrs]/112-[HC])/0.5)*0.5)

Posting Permissions

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