Selecting Random Records

Topic

New Reply

Is there any way you can make a random selection of say 50 records from a database?

Reply | Quote

Replies

Roberta:

There sure is; check out the Rnd( ) function as well as the Randomize statement in Access help. If you have questions, post back.

A note of caution: I started to develop an app for my company to use for conducting random drug tests of its commercial drivers. After chatting with the folks in HR, we decided to use a 3d party product to minimize our legal liability. I’m not sure that this particularly applies to you, but…

Reply | Quote

This is one thing that I was wondering about. Say that I wanted to generate a random list of 100 out of 1000 records, using a Rnd() function. Rnd() is just that – random. Therefore, does there exist the potential that I might end up with ~90 unique records, and some duplicate records, for the total of 100? If so, how can this be avoided? (A first thought would be to query the results of the random selection, grouping & counting records, and redo if the record count doesn’t work out right… but this is rather ponderous.)

Switching roles from IT staff to Auditing staff, I would be interested in using some sort of random record selection process to pull lists of transactions to audit. However, I would need some assurance that the selection process worked efficiently with no potential for duplication.

As a side note: I strongly agree that the potential legal liability must be considered wherever a random selection may have additional repercussions.

Reply | Quote

All the Rnd() function does, once you pass it a seed value, is generate a pseudo random number. If you sort on that number and use a top values query to give you the top 100, that should produce 100 random records. The only way you might get duplicates is if your underlying recordset contains duplicate records, which means you didn’t design something properly in getting to this point. If you use a Select Distinct in the underlying query, there should be no possibility of duplicate records.

Reply | Quote