Maximum value multiplied by constant not to exceed limit (1 Viewer)

[Cosmos75]

I have a Query that performs calculations and lists out items and a value. Some of the items are repeated and have different values.

e.g.

A 6
A 4
A 8
B 12
B 6
C 4
D 4
D 11
E 4
E 16
E 7

I need to multiply all the maximum value for each letter by X1 (X1 = 100). Simple enough so far….

Problem is each letter has a value that that it cannot go over (A 750, B 1300, C 550, D 500 and E 1800). So, A, B, and D exceed their respective limits.

So in this case I need to rerun the calculations for A, B, and D using another X2 (50) AND recalculate A, B and D using the second highest value multiplied by the original X1 (100). And if that goes over, then I need to use the third highest and so on. Then the sum of A (Highest) * X1 (100) and A (Second) * (50) can’t be over the limit for A (I can readjust X2 till it does).

How do I do this? It’s a bit more complicated than this, but it’s a start!




[This message has been edited by Cosmos75 (edited 05-06-2002).]

 

[dynamictiger]

Whilst it may be possible to nest if statements to acheive a quasi close outcome, it would probably be more practical to write a code module to achieve the desired result.

You would probably pass your limit and then loop through the sum until the answer is less than limit.


The code would be something like:

MyFunction(Value,Limit as Single, Multiplier, OtherMultiplier as Single)

'Limit = 750
'Multiplier=100
'OtherMultiplier=50
'Value =8

if value * Multiplier>Limit then

NextFunction(OtherMultiplier)
Else

value * multiplier
end if

end function
NextFunction(OtherMultiplier)
Dim intCount as Integer

intCount=OtherMultiplier

For each intCount

Value * intCount

if value*intCount<Limit then

ExitFunction
else

intCount=intCount-1

end if
Next
End function

This code is not right, but is the general idea.

 

[Cosmos75]

I will try that out soon, am not familiar with Access VBA to change it but I'll give it a whirl and post back.

THANKS!