Dear Allen,
Blush! Yea, sure. I probably learned it reading from a post you wrote a
few years ago. Now, how's that for you scratch my back, I'll scratch yours.
(Or was that a post by John Vinson? Hmmm? Not sure I recall.)
You MUST seed the RND() function. If you don't have any numeric column in
the table, use the LEN() of an alphanumeric column. Anything that has the
potential to be a variable number will avoid the "optimization feature" that
considers the RND() function to be determinant if it doesn't have a
potentially variable argument. When this feature kicks in, which is meant
to improve performance (and probably does in some cases), you will get all
the random values identical. This would defeat the purpose for which we use
it to randomly sequence rows.
Don't forget there's at TOP 10 PERCENT (or TOP XX PERCENT) capability to get
a proportion, rather than a fixed number of rows.
Final note: In the quite unlikely event that the random number function
gives you 2 rows with identical values (very unlikely I hope) it is just
barely possible you could get an additional row, more than you asked for.
If you add a unique column to the ORDER BY, putting that after the random
one, it would act as a tie breaker and make this rather unlikely possibility
into an impossibility, although this does introduce the slight posibility of
a slightly non-random skew to the selection.
Allen caught the fact that I failed to give the name of the random function.
I figured it wouldn't hurt you to go find it, an exercise in the use of
online help. Besides, I wasn't sure I remembered it exactly, and why should
I have to do all your work for you! I get confused, since I use MSDE in
nearly all my work, and things are different there. But, I assume you're
using Jet. That's a safe assumption here in this newsgroup about 98.44% of
the time, just like the soap. But also, just in case, it's better to let
you do the help lookup on the product you're actually using, rather than
risk giving an answer that is for Jet to someone who's actually working with
MSDE. So, my answer was generic as well.
Tom Ellison
Allen Browne said:
Adam, you won't get a better answer than Tom's. His knowledge of Access
and its queries is outstanding, and his recommendation is exactly what I
use.
If you need an example of what to type in the Field row in query design
(assuming a primary key named ID):
Rnd([ID])
Don't forget to execute a Randomize in the session before you run this
query.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
ADAM said:
Thanks for the response Tom, i will try this on monday. If anyone else
would
also like to make any suggestions, i will take all and try them and see
which
is the best for my situation. sorry about the caps thing.
