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.
