Multiplying Random Numbers

[Jabari Powell]

Hi, I am trying to determine if it is possible to multiply two fields that I have generated in Access using the random function. The result always seems to be another random number which uses the same range that I have for the two previous fields.

Ex. Expr 1: Int((6-3+1)*Rnd()+3)*100
Expr 2: Int(((2-1+1)*Rnd()+1))*3

I want to be able to return the result of the numbers that were generated using these, but if I try the straight forward:

Expr 3: [Expr 1]*[Expr 2] it just returns a random number that uses the same range as those numbers.

[Stewart Ross]

What you have posted is not really something that makes much sense, particularly when you have retained expressions such as (6-3+1) which should have been simplified out.

Expr 1 simplifies to 100 * Int(3 + (4 * Rnd)), which will generate a random number in the range 300 - 700. Expr 2 Is just 3 * Int(1 + (2 * Rnd)), which will generate a random number in the range 3 - 9. Multiplying the two expressions will generate a random number in the range 900 - 6300, which could have been generated by a single expression in any event.

The Rnd function will generate new random numbers on every occasion it is called - these will not be the same as in the individual expressions, so (Expr 1 * Expr 2) will not be the same as the product of the two original calculations. As the range of the random number generated will be within the bounds stated above (900 - 6300), what's the problem?

The ranges mentioned must have some significance in whatever you are doing, but you haven't explained what they mean or why you need these range boundaries.

-Stewart

[NeoPa]

Interesting, and I would add surprising (to me at least). I would have expected the resultant values to be saved and reused by the SQL engine rather than simply saving the formulas to be re-evaluated. Certainly interesting.

[ADezii]

@Stewart:
Forgive me if I am mistaken, but aren't your Upper Ranges off, since Rnd() can never return a Value of 1:

100 * Int(3 + (4 * Rnd)) 
Lower Range: 300
Upper Range: 600

3 * Int(1 + (2 * Rnd))
Lower Range: 3
Upper Range: 6

[NeoPa]

You're not mistaken ADezii. It means that Int(Rnd() * X) always returns an integer value in the range of X integer values starting from zero (EG. Where X = 4 then the range of values is 0, 1, 2 and 3).

This detail doesn't change the purport of what Stewart was saying, but is nevertheless an important point to consider when generating code for random integers within any range. Using Int() is also a good way to ensure that each possible integer in the range has an equal occurrence probability (and not all code that uses Rnd() does).

[Stewart Ross]

@ADezii - darn, mistakenly forgetting that the range for Rnd does not include 1.0 itself!

@NeoPa, the Rnd() function recalculating whenever it is called is also a feature of the implementation of the similar Rand() function in Excel. It can result in some interesting side effects. For example, if a sort is applied to a set of random numbers generated by placing =Rand() in each cell, the values in the cells change completely but the end result (at least as displayed) is not in sorted order at all. This is because the random numbers are recalculated during the process (after the sort, from the testing I've done).

-Stewart

[NeoPa]

Weird stuff - but it makes sense unfortunately :-(

[Jabari Powell]

Thanks for the help guys. So given everything you all have said, the short answer is no correct?

I was hoping to be able to use Access to generate some random numbers for something I'm working on. I was hoping to be able to also have the check numbers based on those random numbers, but it seems like I'll have to find a work around for that now.

I didn't simplify it out because I have been changing the boundaries based on the results that are generated and what I expect, and it's easier for me to just put in the upper and lower boundaries rather than doing the math - especially when the DB will do it itself. Expr 1 is the value for a period, Expr 2 is the number of periods, and I wanted Expr 3 to be the total value.

[NeoPa]

Jabari:
I didn't simplify it out because

That makes some sense within your project, but not really any when posting a question for people to help you with.

All is not lost. This is a (very rare) situation where it makes sense to add your data into a temporary table held specifically for this task. Once the values have been created they can then be processed from this table with the clear understanding that no further calls will be made to recalculate the values as you try to use them.

Make sense?

[Jabari Powell]

I'm not a programmer but it does make sense. I don't know how to do that right now, but I'll look into it and see how it comes out.

[Jabari Powell]

Got it to work - did what you said, created a table, then turned the query into an append query. So I can run a query off that table and come out with the total cost. And now all I have to do is run a macro that deletes the data and then runs the append query each time I want to generate new data.

You're a GENIUS!

[NeoPa]

Very kind of you to say so, but it seems you're not too much of a slacker yourself if you moved on to a solution with just that hint.

I'm not a big fan of Access' macro system myself, but VBA code can do the same thing of course with much better control. The deletion can be done with a simple DELETE query and the appending can be done with an INSERT INTO query. Let us know if you struggle with either as, it seems, this is somewhat new to you.

[Jabari Powell]

That hint was really the solution I was looking for. I've done the delete and append queries that can generate that data for me.

You can take a look at my next question Can I generate a random number of sales this way? and tell me what you think about that. I have no idea how to go about it at this point in time.

[Mihail]

Why do not use only a Make table query instead all this additions and deletions ?

[NeoPa]

Make table queries have a habit of getting out of control. They're fine for one-off situations, but when you are working within a database and doing the same work repeatedly you typically want control over the design of what you're working with. Also, you don't want the additional overhead of deleting and creating the same table every time. It saves you nothing whatsoever and adds to your overhead while reducing your control. Deleting and appending records is a far more appropriate solution.

The alternative of creating tables without deleting them when done with should not even be contemplated.