Access Cookbook

Use a Query to Retrieve a Random Set of Rows

Problem

You need to be able to retrieve a random set of rows from a table or a query so you can identify a random sample for a research study. You can’t find a way to make this happen in the normal query design grid. What’s the trick to getting a random sample of a certain number of rows?

Solution

The solution to this problem is not quite as simple as it might first appear, because of the way Access attempts to optimize the use of function calls in queries. You can call a VBA function to generate a random value for each row, but to ensure that your function runs for each row, and not just once, you need to feed it a value from the row. Once you’ve generated the random numbers, you can sort by that random column and use a Top Values query to select a random group.

In 01-07.MDB, open tblRandom. This table includes 50 rows of data. Your goal is to pull five randomly selected rows for this set of data. To do this, follow these steps:

1. Import the module basRandom from 01-07.MDB or create your own, including this single function:

   Public Function acbGetRandom(varFld As Variant)
      
      ' Though varFld isn't used, it's the only way to force the query
      ' to call this function for each and every row.
   
      Randomize
      acbGetRandom = Rnd
   End Function

2. Create a new select query or use an existing one. Add any fields you’re interested in.

3. Add an extra column, with the following expression replacing the reference to the State field with a single field in your query’s ...