Returning Random Records

Returning Random Records

If you want to return 10 records at random from a table, you might be tempted just to use the Rnd function in a query. However, this will then only be evaluated once in the query, giving the same value for each record in the table. Instead, you will need to use Rnd on a field, and then sort by this field.

For example, suppose that you have a table, tblName, that has two fields, FieldPK (AutoNumber) and Field1 (Text), and you wish to return 30 random records. Start by creating a new query, and adding tblName to the query. In the first column, type:

Expr1: Rnd([FieldPK])

And then add the other two fields to the query grid. Set the query to be sorted by Expr1 (it doesn't really matter if it is ascending or descending), remove the Show Value tick for this field, and any other sorting that you might require on the data. In the Top Values combo box on the Query Design toolbar type in 30, to return the Top 30 records. Now, when you run this query, you will get 30 records returned at random. The SQL for this query will look like:

SELECT TOP 30 tblName.Field1
FROM tblName
ORDER BY Rnd([FieldPK]);

If you don't have a numeric Primary Key to perform this on, instead having a text field as the Primary Key, then you can use something like this:

Expr1: Rnd(Len([FieldPK]))

As the Len function will return a number that can be evaluated for each record in the query.

Top

Last modified at 06/06/2006 14:58:55