Microsoft KB Archive/128874

= ACC: Find N Records in Random Order =

Article ID: 128874

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q128874



Novice: Requires knowledge of the user interface on single-user computers.



SUMMARY
This article shows you how to shuffle the records in a table in random order. It also shows you how to get  records from a table at random (where  is the number of records that you want). You can use this method to make sure that the records will never be repeated.



MORE INFORMATION
To shuffle the records in a table, create a query based on that table and all its necessary fields. Add a calculated field that contains the Rnd function with a positive integer variable parameter. Then, sort by this calculated field. Every time you evaluate the query, the records will be shuffled.

To get  records at random, set the query's TopValues property to the number of records you want.

The Rnd function requires a numeric argument to return a random number. If the argument is greater than zero, the next random number in the sequence is returned. Because of the way queries are optimized, if you create a calculated field with a numeric argument such as

Expr1:Rnd(1)

the query calculates the Rnd function once and then repeats the same value throughout the recordset. To avoid the repetition, create a calculated field with a variable numeric argument. For example, if the table has an Employee ID field, you can use the following expression as the variable numeric argument:

Expr1:Rnd([Employee ID])

If the table does not have a numeric field, write an expression such as the following to calculate a numeric value based on any other field:

Expr1:Rnd(len([First Name]))

The Rnd function ignores the expression, but the variable nature of the argument forces the query to evaluate the Rnd function for every record.

