Portland Access User Group

Portland Access User Group Conference

September 28-30, 2019

The PAUG Database Designer International conference brings together a wide range of Access developers, consultants, power users and Access enthusiasts. This marks the 21st anniversary of the conference. We will once again be returning to the peaceful and natural surroundings of the Conference Center at Silver Falls State Park, which lends itself to a climate that fosters learning, creativity, and socializing.

Monday, December 14, 2009

Top Query Revealed: Top Query to find Random Records

Top Query to find Random Records

This is the fourth in a series on the Top Query. In previous posts, I've discussed Simple Top Queries, Aggregate and Grouping Top Queries, and the Problem of Ties in a Top Query.

One rather surprising use for a Top query is to return a resultset of randomly selected records. It's also surprisingly simple. All you really need to do is sort on a random number, or rather, generate a random number in the Order By clause of the query.

So how do you get a random number? Fortunately, Access has a built in function called Rnd that will supply one.

To use Rnd, you need to supply it with a seed or an initial value to generate a pseudorandom number. Any numeric field will do for a seed value, but I generally use an autonumber, primary key field. In the case of the Orders table, that would be OrderID, but OrderNum would have worked as well.

SELECT TOP 10 Account, OrderNum, [Pairs Shipped], [Total Price]
FROM Invoices
ORDER BY Rnd(OrderID);

Every time this query is run, it will return 10 different records, well, sort of. Used as is, the Rnd function will produce a different set of records each time. However, if you close the database and re-open it, it will run the same records in exactly the same sequence.

In order to get a truly random set of numbers each time, you have to add the Randomize command. To do that, you have to create your own function. In a global module, create the following function:

Public Function gRnd(FeedNum As Long) as Double
Randomize
gRnd = Rnd(FeedNum)
End Function

Then use your user-defined rounding function in your Order By clause:

SELECT TOP 10 Account, OrderNum, [Pairs Shipped], [Total Price]
FROM Invoices
ORDER BY gRnd(OrderID);

A free sample illustrating this process can be found here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=233

Next time, I'll finish up the Top Query by looking at Creating a Parameter for Top Value.


No comments: