Access RND function, keeps repeating the same random number ?

[johnseito]

I enter this SQL statement

SELECT id, RND()


FROM TableExample

and I get a random number that is the same repeated, example of the below number (repeated).

0.817580938339233


However, if I change my SQL statement to:

SELECT id, RND(id)
FROM TableExample

I get a random number that is different for each line, as an example of below:
Any idea why that is ? That doesn't make sense to me. Thanks !!! :-)

0.484300733
0.619954884
0.989054441
0.241069734
0.344581962
0.642870963
0.359026909
0.064990222

[ItsMe]

I do not know for sure but it seems the id in "RND(id)" is reseeding the rnd() function.

I usually use the rnd() function in VBA; actually, I rarely use the function. The point is you need to tell the rnd() function to look at something new. In VBA, I reseed the function by using Randomize.


dim sglAnswer as Single
Randomize
sglAnswer = rnd(2)

or

dim sglAnswer as Single
Randomize
sglAnswer = rnd(12)

to avoid getting a return of zero
dim sglAnswer as Single
Randomize
sglAnswer = (rnd(12)) + 1


Calling Randomize in VBA reseeds the rnd() function by referencing the System's clock and using that Double in its formula/algorithm. If you do not call Randomize, you will get a more predictable result.

Perhaps using a function in a Standard module and calling the function from your query is the best solution. There is a simple example in the answer to this thread.
http://stackoverflow.com/questions/6...rd-true-random

[johnseito]

ItsMe,

Ok thanks for your information.

So what is the difference between these two code from you ?
They look the same to me.

dim sglAnswer as Single
Randomize
sglAnswer = rnd(12)

or

dim sglAnswer as Single
Randomize
sglAnswer = rnd(12)

Also I never really use VBA in access as much at this point so I am limited. Is Access VBA, the same as Excel VBA ?
So base on what you said and logic, if I duplicate some of the id, the RND should pop out duplicate random number as well ?

I tested it, however, the random number is different.

SELECT id, RND(id) 
FROM TableExample

Thanks !!!

[johnseito]

example: .......

ID	RandomNumber
46434	0.748555303
46434	0.408245862

[ItsMe]

I meant to post different values for the Number argument in the Rnd() function. I edited my post.

Perhaps you can explain what your question is again or state your goal by implementing the Rnd() function. When I use the Rnd function, I do not bother using a Number argument. I believe all that does is limit how big the random number returned can be. So the following two examples would be similar. The difference is that the second one produces results that are less predictable.

Dim dblMyDouble As Double
Randomize
dblMyDouble = Rnd(7)
MsgBox dblMyDouble

or

Dim dblMyDouble As Double
Randomize
dblMyDouble = (6 * Rnd()) + 1
MsgBox dblMyDouble

[johnseito]

Perhaps you can explain what your question is again or state your goal by implementing the Rnd() function.

I looked at a code (not mine) where it looks like this:

SELECT id, RND(id) as RandomNUMBER
FROM TableExample

This code base on my logic and the logic I see behind it, I believe is redundant.
So I say why do we need id in RND ?

so I re-created the SQL statement like this (without id in RND):

SELECT id, RND() as RandomNUMBER
FROM TableExample

However, after doing this, I see the RandomNUMBER being the same (repeated) for all the rows.
That doesn't make sense to me because I would think it would give different numbers for
each row.

so then I posted on this forum to ask for some answers.

You answered and base on your answers, then I say what if I have two id that is the same,
would I have a randomNumber that is the same as well ? I tested it, it turned out, that, that is not true,
so that puzzles's me as to how the RND function works in the SQL statement ?

So we are here !!! :-) :-) :-)

[ItsMe]

If you do not pass a parameter to the function, the function uses the System's timer as part of the formula employed (the seed). To ensure that the function is reseeded, Call "Randomize".

If you do not pass a parameter to the function, it will return a value between 0 and 1.

If you pass a parameter to the function, it will use the number you provided as the seed. Depending on whether or not the parameter you pass to the function is equal to, less than, or greater than 0, determines how the function behaves. I believe that using a number greater than 0 will limit the value returned to said number plus one fraction of one whole.


If you want a not so predictable return. Use VBA and Randomize. Also, include some simple algebra to produce a less predictable result.
dblMyDouble = (6 * Rnd()) + 1
or maybe
dblMyDouble = (6 * Rnd(90000)) + 1

[orange]

Here's a link to more info -- RANDOMIZE is the issue as was suggested.

[Xipooo]

To the OP, do you want a new random number to be generated each time you run the select query? That seems like a rather odd thing to do. Or do you want a random number to be created at the time the record is created? Then you can store that random number into your table and retrieve it later.

I guess maybe if we knew more about what you were trying to accomplish we could find a better way to skin this particular cat.

[johnseito]

Ok, thanks for your info. I am trying to grasp what all of what you said. :-)