Nested If Statement returning Exceeds Resources Error

[DavidK111]

Hello - I am simply trying to update the value of a retail price, based on the sales price, for a large number of products. I am hoping to use some sort of randomization so that the retail prices don't simply show the same number marked up for every single product.

So, as an example, I am hoping to have the below (somewhat random) results for 5 sample products:

(1) Sale price of $36, Retail price (calculated value) of $49.99
(2) Sale price of $78, Retail price (calculated value) of $109.95
(3) Sale price of $79, Retail price (calculated value) of $99.99
(4) Sale price of $32, Retail price (calculated value) of $69.95
(5) Sale price of $15, Retail price (calculated value) of $19.99

I created a nested if formula that will probably work. It uses a random value column that I have assigned to each product (so that it marks up a different number depending on whether the random value is between .01 and .25 or between .26 and .5 or between .51 and .75 or between .76 and .99 (i.e, four different markup).

However, when I run the query I am getting an Exceed Resources Error. This is probably because I am overcomplicating things a bit, or perhaps because using a nested if formula is too difficult for Access's resource limitations.

Does anyone know of a better way for me to accomplish this result? Is there a way to simplify the formula or perhaps to use an array or "pricing table" of some sort.

(Feel free to ignore this next paragraph if it is too much detail. If it helps, here is the formula that I created. The formula I used also sets a smaller markup for products that have a lower set retail price and a larger markup for products that have a higher set retail price. I can't simply use the set "original" retail price because many times the current price is HIGHER than the original retail price...and thus I have a need to increase the retail price. It is also preferable that the retail prices is rounded to a 10 (such as 39.99 or 39.95 as opposed to something like 36.99). For clarity, the InputRetail price shown in the formula is the original retail price (which I am happy to use whenever it is at least 30% higher than the sale price (i.e., "aprice"). That is what is happening in the first part of my formula which is essentially using the Original retail price (i.e., Input Retail) whenever it is significantly higher than the Sale Price. But if that step is confusing/complicating things, I am okay to ignore the original Input Retail price and just use a simplified markup formula.)

Thanks for any suggestions to help me simplify this formula or to somehow achieve the desired result (i.e. of simply marking up the prices to a higher, somewhat random retail price).

IIf(([inputretail]-[aprice]>20) and [inputretail]<50,[inputretail],IIf(([inputretail]-[aprice]>50) and [inputretail]>50,[inputretail],iif(([[inputretail]<45) and([Rand]<.25),10*Round(([FinalABWPrice]+20)/10,0)-0.05,iif(([[inputretail]<45) and([Rand]<.5),10*Round(([FinalABWPrice]+30)/10,0)-0.01,iif(([[inputretail]<45) and([Rand]<.75),10*Round(([FinalABWPrice]+20)/10,0)-0.01,iif(([[inputretail]<45) and([Rand]<.25),10*Round(([FinalABWPrice]+30)/10,0)-0.05,iif(([[inputretail]<5000) and([Rand]>.75),10*Round(([FinalABWPrice]+70)/10,0)-0.05,iif(([[inputretail]<5000) and([Rand]<.5),10*Round(([FinalABWPrice]+90)/10,0)-0.01,iif(([[inputretail]<5000) and([Rand]<.75),10*Round(([FinalABWPrice]+100)/10,0)-0.01,iif(([[inputretail]<5000) and([Rand]>.75),10*Round(([FinalABWPrice]+80)/10,0)-0.05,10*Round(([FinalABWPrice]+110)/10,0)-0.05)))))))))

[DavidK111]

If it helps, here is the actual query and Field names I am using (See pic)

[Welshgasman]

I would create my own function.

[Minty]

Based on a quick look this would be best handled by joining the values to a simple look up table to get your desired multiplier.
It is also the most efficient way as you aren't running a function on each value.

[Micron]

36 becomes 49.99 but 32 becomes 69.95? Surely then the logic is based on something other than mathematically applying a price increase based on some sort of scale.
I'd agree that it's way over complicated and might be prudent to point out that such an IFF expression, loaded with built in function calls, is a bad idea. All parts of an IFF expression are evaluated, regardless of whether or not the first part is T or F. So you're asking your poor pc to do a TON of work. If the table idea doesn't appeal to you, perhaps explain the logic behind the increases for more help.

[DavidK111]

Thanks for the suggestion. That sounds like a good way to do it. Is there straightforward way to have the lookup table populate values based on ranges? Or perhaps do you know where I could find a sample lookup table to see how the relationship would actually work for all of the different potential prices of my products?

And I am guessing this won't be possible but do you know of a way to achieve randomization using a lookup table (so that varying markups are applied)?

Thanks!

[Minty]

You would have a table something like

ID	LowValue	HighValue	Multiplier	Randomiser
1	£0.00	£29.99	1.5	?
2	£30.00	£44.99	1.4
3	£45.00	£59.99	1.45
4	£60.00	£84.99	1.3
5	£85.00	£20,000.00	1.25

I'm not sure how I would do the random bit....

The join is quite straightforward.