ACC: Find N Records in Random Order

 Last reviewed: April 25, 1997
Article ID: Q128874
The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

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

MORE INFORMATION

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

To get <N> records at random, set the query's TopValues property to the number of records 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 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.

REFERENCES

For more information about returning a random record please see the following article in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q108435
   TITLE     : ACC: Sample Function to Return a Random Record
For more information about the TopValues property, search for "TopValues," and then "TopValues Property" using the Microsoft Access 97 Help Index.

Keywords : kbusage QryGnrl
Version : 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 25, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.