How to find N records in random order in Access 2002

Article ID: 287682 - View products that this article applies to.
This article was previously published under Q287682
Novice: Requires knowledge of the user interface on single-user computers.

For a Microsoft Access 97 version of this article, see 128874
(http://support.microsoft.com/kb/128874/ )
.
For a Microsoft Access 2000 version of this article, see 208855
(http://support.microsoft.com/kb/208855/EN-US/ )
.
Expand all | Collapse all

SUMMARY

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

MORE INFORMATION

To shuffle the records in a table, create a query that is 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 that you evaluate the query, the records will be shuffled.

To get <n> records at random, set the TopValues property of the query to the number of records that 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 that is 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.
Back to the top | Give Feedback

REFERENCES

For more information about the TopValues property, click Microsoft Access Help on the Help menu, type topvalues in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Back to the top | Give Feedback

Properties

Article ID: 287682 - Last Review: August 11, 2004 - Revision: 1.0
APPLIES TO
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbhowto kbdta KB287682
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top