Questions on Randomising

[doma23]

Hey guys!
I'm newbie and I'm trying to understand how this works.
Everything is clear and only thing I don't understand is why the order of the newly generated random numbers is random?
If these are the generated random numbers: 3,10,5,2
Why the order is not 2,3,5,10 instead of random?
Tnx!

**Edit**
This thread was split from a related thread at How to delete a record from recordset but not from table.

[NeoPa]

Hey guys!
I'm newbie and I'm trying to understand how this works.
Everything is clear and only thing I don't understand is why the order of the newly generated random numbers is random?
If these are the generated random numbers: 3,10,5,2
Why the order is not 2,3,5,10 instead of random?
Tnx!

I guess this would be down to the nature of random numbers and sequences thereof. If it were predictable that the next number in the sequence were greater than the previous, then that very predicatability would preclude the concept of randomness.

[doma23]

I'm not sure you understood me well.
After calling randomizer function I have this query:
Number Random
1 63
2 321
3 801
4 102
5 200

And now, if I'm going to sort it by RANDOM ASCENDING, I should have this:
Number Random
1 63
4 102
5 200
2 321
3 801

Number column would still be always random.
Instead, it seems it doesn' matter whether I choose ASCENDING OR DESCENDING, as long as I choose SORT BY it will sort RANDOM by some unknown rule.
That's what I don't understand.
Or you wanted to tell me that Access recognizes wheter the number were generated by using rnd function, and therefore when SORT BY is included it just sorts those numbers randomly?

The other thing I don't understand is why hype261 put the "* number" in randomizer function: randomizer = Rnd() * 100 * number ?
Futhermore, if there is a "* number", how come the function never returns numbers higher than 999?
If the rnd() function gives a random number from 0 to 10, ie. let's say it returns 6 for the number 4, than it should be randomizer = 6 * 100 * 4 = 2400.

Hope I was clear, and sorry if I'm adding to the topic whose author already found solution, but I really find this interesting and I would like to know how it really works?
Thanks!

[NeoPa]

I think I understand a little better now.

I have no idea why the Random column doesn't seem to sort correctly.
Rnd() returns a real value which is between 0 and 1. The range of numbers is always handled by multiplication by the range it should span. IE. A random number between 15 & 20 is (Rnd() * 5) + 15.
I have no idea why Number was included within the original code.

We probably need to leave this here or split into a new thread. Let me know if you're interested in continuing further.

[hype261]

I think I understand a little better now.

I have no idea why the Random column doesn't seem to sort correctly.
Rnd() returns a real value which is between 0 and 1. The range of numbers is always handled by multiplication by the range it should span. IE. A random number between 15 & 20 is (Rnd() * 5) + 15.
I have no idea why Number was included within the original code.

We probably need to leave this here or split into a new thread. Let me know if you're interested in continuing further.

The reason I included Number in the original code was because if I didn't the randomizer function seemed to be evaluated only once for the whole query which wouldn't give a random order at all.

I believe why the order by is messed up is because Access is evaluating the randomizer twice for each row. Once for the column and once in the order by function. I could be wrong on this one though.

[doma23]

The reason I included Number in the original code was because if I didn't the randomizer function seemed to be evaluated only once for the whole query which wouldn't give a random order at all.

I believe why the order by is messed up is because Access is evaluating the randomizer twice for each row. Once for the column and once in the order by function. I could be wrong on this one though.

Hmm...that's weird because I'm getting random number only with Rnd()*100. Actually, I believe that even just plain "rnd()" is enough.

For your point that it might be because Access evaluates it twice, I guess it might be true. That satisfies my hunger for explanation. :)

[hype261]

Hmm...that's weird because I'm getting random number only with Rnd()*100. Actually, I believe that even just plain "rnd()" is enough.

For your point that it might be because Access evaluates it twice, I guess it might be true. That satisfies my hunger for explanation. :)

If I did the following query...

SELECT Table1.pk, Table1.text
FROM Table1
ORDER BY Rnd();

The results would always be the same. This leads me to believe that Rnd is only being called once for the entire query. Though if I do...

SELECT Table1.pk, Table1.text
FROM Table1
ORDER BY Rnd([pk]);

I get random values and according to MSDN...
Rnd[(number)]

If number is greater than zero the next random number in the sequence.

[NeoPa]

Now you can explore the issue to your hearts' content :)

[Stewart Ross]

When Access evaluates any function supplied as a calculated field in a query, unless it is forced to re-evaluate the function for every row in the query it is evaluated just the once. You can verify this by including in a test query an unbound calculated field with a call to the date/time function Now(), for example. You will see if you do so that the calculated field returns the same date/time value for all rows in the query until you re-run the whole query once again.

This behaviour is quite different to having a bound field in a table - say [Update Time] - with a default value of Now(). The function is evaluated for each row when there is such a dependency.

From the point of view of the query engine, if a function call 'stands alone' and has no dependency on field values for the current row, why re-evaluate it each time?

As you have found with Rnd(), supplying a row-dependent value to the function (if such an argument is allowed) forces re-evaluation for each row.

-Stewart

[Stewart Ross]

... and here's a link to a thread that ADezii and I answered which is similar to the original approach in the thread from which this was split, and which shows the use of a public function supplied with a dummy value to force re-evaluation of the Rnd() function:

http://bytes.com/topic/access/answers/869437-select-every-79th-records

-S

[doma23]

... and here's a link to a thread that ADezii and I answered which is similar to the original approach in the thread from which this was split, and which shows the use of a public function supplied with a dummy value to force re-evaluation of the Rnd() function:

http://bytes.com/topic/access/answers/869437-select-every-79th-records

-S

Thank you very much Stewart.
I've read the other topic and you perfectly explained the issue there.

[NeoPa]

It was a bright day indeed when Stewart joined our ranks.

Nice explanation :)