Return a Random Selection (Access 97)

Topic

New Reply

I know that this isn’t strictly what you’re meant to do in a database but hey (!)

I need to get a random selection returned from a table. I don’t care how.

I was thinking about creating a temporary recordset and then using some kind of random function to return only certain (“randomly” selected) records.
I’m getting along with this quite well but thought I’d poll peoples opinion on the best (or better) ways of doing this.

Ideally I’d like to get to a result that returns a user specific number of records (say from 1 – 100) but that these records are returned at random from a base table.

You thoughts would be appreciated!

Reply | Quote

Replies

Dev Ashish has an article on the Access Web here.

HTH,

Tom

Reply | Quote

The article from the Access Web gives you one method of doing it. Still another is from within a query. Here’s an example of a query based on the Orders table in the Northwind database. It returns 100 pseudo-random (which is about the best you can do) Orders records.

SELECT TOP 100 Orders.*
FROM Orders
ORDER BY Rnd([OrderID]);

You have to pass Rnd() a value that will differ between records, so an autonumber key is a good choice, but most numeric keys will work if there’s a reasonable variation between records.

Reply | Quote

Thanks…

Two very useful methods. I think I’m goignt ot use all three (my module got finished but is only useful in some circumsatces)

Reply | Quote