Forum Discussion
If Statement returning Exceeds Resources Error
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 ( four different markups).
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.
This is using Windows 8.1 and Access 2010 FYI
2 Replies
- maybe show us your formula so we can Trim it a bit or modify it.
or maybe you can use a Lookup table with "limits" (low and high) and based your Update query
on that limits. Let's start with the business rule behind this requirement.
" 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."
I'm not a retail expert, but it does seem unusual to me that a business would randomly and arbitrarily change prices for a number of different products. What is that business rule? Why do price changes need to be random? The very notion seems, well, arbitrary, to me, but without more insight, it's better to hold off on further discussion.
I think you would want to do this with a recordset in VBA, though, if it really does need to be such an arbitrary change. You'd want a table of price ranges and price change percentages, I think, rather than just a random percentage.
