Can I generate a random number of sales this way?

[Jabari Powell]

Okay, so I'm back with another conundrum I hope you all can help me with.

I am trying to generate a random number of units sold (for a single sale) that is equal to a randomly generated percentage multiplied by (total purchases - total previous sales).

The problem is that I can't sum the previous sales if I am trying to generate these numbers in one query.

So I guess my question is: Will this process be able to be completed using Access?

To better understand it goes like this:

For Sale 1, it should generate a random number based on the %*total purchases. So:
Sale 1 = (total purchases)*%
Sale 2 = (total purchases - sale 1)*%
Sale 3 = (total purchaes - (sale 1 + sale 2))*%

% is just the percentage of inventory that i want the company to sell.

[NeoPa]

You mention "Random" in the question but nowhere is it clear where it fits in.

How to handle reuse of random numbers in your query was fully answered in your other thread. Why are you asking what is essentially the same question after indicating you were already happy you had an answer? I'm thoroughly confused.

[Jabari Powell]

Well the difference in the first question and this one is the formula portion. The problem is not with the random numbers this time, but rather with being able to multiply that number by the (total purchases - total sales) that occurred before the given sale date.

I've been using a query to generate the random numbers, so that means the totals for the sales before that date would be generated at the same time as the number for that date. So if I try to run an append query, it would first generate the numbers and then append them to the sales table - which wouldn't take into account the sales that occurred before a given date

[NeoPa]

I still see no question that makes sense here I'm afraid. Even if you're happy to describe a value as random that is already available in a field within a record of a table, that still doesn't help the question to make any sense.

[Jabari Powell]

I totally understand why its difficult to see the sense of the question - especially given that I'm not really sure what the technical aspects of it are. I've abandoned this segment for now and just going to a more simplistic one.

If you notice that Sale 2 includes sale 1 as part of its formula, but these are all being generated in a query at one time. That means all of the numbers are generated at one time so it won't be possible know what sale 1 is for sale 2 - and therein lies the problem.
Sale 1 = (total purchases)*%
Sale 2 = (total purchases - sale 1)*%
Sale 3 = (total purchaes - (sale 1 + sale 2))*%

What I've done instead is just generated a set of random numbers that will end up being less than or equal to the previous purchase.

I've rationalized that in order for me to complete the formula the way I would want, I would have to code it in VBA and create a variable that would hold the values for me that I could then use in the calculation of the formula. But since I'm not a programmer it would be nearly impossible for me to learn how to do that and do it in the time frame that I am hoping for.

[Stewart Ross]

Like NeoPa, I'm not clear what the purpose of the random numbers is in the context of sales etc. Is it some form of simulation in which you are testing randomised totals for sales against proportions of sales generated by particular products?

Anyhow, it seems to me that you'd be better off trying these kinds of 'let's see' scenarios in Excel, which is built for the task of modelling.

Something you may wish to explore in Excel (which you can't do in Access) is its ability to work backwards from an expected outcome to the value of a specified variable necessary to generate that outcome (goal-seeking). It may suit what you need better than the approach you are taking at present.

-Stewart

[NeoPa]

It may help for me to say here, that any modelling done using the Rnd() function can only work reliably if the results of these calls are saved into a table before they are ever used in any calculations. This is because, as we saw before, any reference to such a result within the same SQL is actually a reference to the original formula (instead of the result itself) so will re-evaluate a new random number for you, which is not what you require.

Does this go some way to clarifying the situation for you?