databasedev.co.uk - database solutions and downloads for microsoft access

Using a Query to display Random Records:

This article details how we can display random records from a Microsoft Access table using the Rnd function in our query. There may be times where we need to see a sampling of records from the table and we might want to return 20 records at random.

If we just apply the Rnd() function in the query, the function is only evaluated a single time, therefore the function will return the same value for each record in the query. The Rnd() function requires a numeric argument to return a random number, therefore the query design would look like the following:

This returns the following results, due to the fact that the function is being evaluated only once for each record:

You will notice how the Expr1 returns the same value for each record, therefore this will not be an acceptable method of returning a set of random records, when combining this with a TOP (n) clause.

What we need to do, to provide an acceptable return of records is use the Rnd() function, but apply this function to a field that we have available. We can then sort on the results of this field, whilst applying a TOP (n) clause.

If we use the same fields as the above example, we can create the Rnd() function and using the EmployeeID, which is an AutoNumber field with a variable number, to seed the function. Our function will now look like:

Expr1: Rnd([pkeyEmployeeID])

This will now return a variable random number, based upon the value in the EmployeeID, as shown below:

Now that we have a random value to use, we can both sort on this field, and also add a TOP clause, to limit the amount of random records that we return. So, if we sort in Descending order on the new expression, and also add a TOP 10 clause, we get the following SQL for the query:

SELECT TOP 10 pkeyEmployeeID, Rnd([pkeyEmployeeID]) AS Expr1, strLastName,
FROM tblEmployees
ORDER BY Rnd([pkeyEmployeeID]) DESC;

This will now give us the following results, which will be different (random) each time the query is ran:

There may be occasions where you do not have a numeric field or Primary Key that you can base the function on. If this is the case, we can still write an expression such as the following to calculate a numeric value based on any other field:

Expr1: Rnd(Len([Last Name]))

The Rnd() function ignores the expression, but the variable nature of the argument, whereby the Len() function will return a numeric value, forces the query to evaluate the Rnd() function for every record. This will display the results as follows, again these will change each time that the query is ran:

The SQL of the Query:

SELECT TOP 10  Rnd(Len([strLastName])) AS Expr1,strLastName
FROM tblEmployees
ORDER BY Rnd(Len([strLastName])) DESC;

The Results: