Access Cookbook - Ken Getz [21]
1.8. 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-08.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:
Import the module basRandom from 01-08.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
Create a new select query or use an existing one. Add any fields you're interested in.
Add an extra column, with the following expression replacing the reference to the State field with a single field in your query's underlying table or query (this query won't run correctly unless you pass one of your field names to the function):
acbGetRandom([State])
You can clear this field's Show checkbox, because there's not much point in viewing a continually changing random number as part of your query output. Set the Sort value for the newly calculated field to Ascending (see Figure 1-21).
Figure 1-21. The sample query, qryRandom, set up to retrieve five random rows
Open the query's properties sheet (make sure the View → Properties menu item is checked, and click on the upper area of the query design surface so the properties sheet's titlebar says Query Properties). Fill in the number of rows you'd like to return in the TopValues property. Figure 1-21 shows the sample query, qryRandom, in design view with the property filled in.
Run the query. Your query grid should show you as many rows as you specified in the properties sheet. If you press Shift-F9, asking Access to requery the data, you will see a different set of rows. Repeating the process will return a different set of rows each time.
Discussion
The general concept behind this solution is simple: you add a new column to your query, fill it with a list of random numbers, sort on those random numbers, and retrieve the top n rows, where n is a number between 1 and the number of rows in your underlying data. There's only one complicating factor: to create the random number, you need to call a function for each row. Access tries to optimize such a function call and will call it only once for the entire set of data, unless the function call involves a field in the data. That is, if you replace the call to acbGetRandom (in Step 3) with a simpler call directly to Access's random number function (Rnd), you'll find that every value in every row will be exactly the same. Access's query engine thinks that the function has nothing to do with data in the query, so it calls the function only once. This makes the random number meaningless, as the whole point of using a random number is to generate a different one for each row.
The workaround, though, is simple: pass a field, any field, as a parameter to the function you call. That way, Access believes that the return value from the function is dependent on the data in each row and so calls the function once per row, passing to it the field you specify in the expression. The acbGetRandom function doesn't really care about the value