How to Query Random Records in Microsoft Access

Today we have a quickie for SQL jockies working in MS Access land: how to select a result set of random records.

Disclaimer

The meaning of the word quickie in this article is two-fold. First, this is a small article — a quick tip. Second, this is not mean to be used for large or frequently-called queries. ORDER BY RAND() is very inefficient. The table I am working with contains only a few hundred records, and I need to generate the result set just once.

How not to do it

Thinking in MySQL terms, my first instinct was to use the following query to produce a small result set of random records.

SELECT * FROM [MyTable]
ORDER BY RAND() LIMIT n

This does not work in Microsoft Access — instead, you need to call the Rnd function.

Querying Random Records in MS Access

The following query is what I chose in order to generate a small, one-time result set of random records. It uses the primary key column ID as a seed.

SELECT TOP n * FROM [MyTable]
ORDER BY Rnd([ID])

Note: in both of these queries, n is the maximum number of records I wish to include in the result set.