Microsoft Access - Retrieving Random Records With A Query (Access 97/2000/2002/2003)

We recently discussed how to retrieve a portion of a query's results using the Top Values feature. You can also use this feature to perform a common task - returning a random selection of records. To do so, create a basic select query based on your desired data source. Then, in a blank Field text box in the design grid, add the following expression:

  • RandomEx: Rnd([valuefield]) where [valuefield] is any field in the data source that contains numeric values--the field can be an AutoNumber, Number, or Date/Time field.

Next, apply a sort order to the RandomEx field (it doesn't matter whether you choose Ascending or Descending). Finally, use the Top Values dropdown list on the Query Design toolbar to specify how many records to return. If you want a number or percentage that isn't listed, simply enter the desired amount in the Top Values text box. When you run the query, you'll find that Access returns a random selection from the underlying record source.

Go back