Random Results

[Lamar]

Each time I run a query I want the results to be a random
5% of the records in the table. I want the results to
change each time.

Is there a way to do this? Thanks for any help.

 

[Allen Browne]

Use the Top Value property of the query to specify 5%.

Use Rnd() to generate a random number for each record. In the Order By
clause, this sorts randomly, so the TOP 5% is a random choice.

Note that you have to pass something unique into the Rnd() function or the
query optimizer does not bother calling the function for every row. You also
need to execute a Randomize some time in the session before running the
query. Perhaps via your AutoExec macro or the Open event of your start-up
form.

The query will end up like this:
SELECT TOP 5% MyTable .* FROM MyTable ORDER BY Rnd(MyTable.MyID);