1. ## 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!)

2. ## 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.

3. ## 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!!!

4. ## 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.

5. ## Re: Calculating the answer (2000)

Please forgive me for the STUPID Question...

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

6. ## 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
•