Random sampling

[uscpa]

I have a huge database of millions of records and would like to get a random sample of 100 rows for testing. Can somebody help me in building a query for random sampling.



Thanks,

[ConneXionLost]

Hi uspca,

Assuming your table has a Primary Key and it is numerical, build a query from your table showing the fields you want to see, and add this as a calculated field:

RandomSample: Rnd([uspcaID])

Sort ascending on this field and set the "Top Values" to 100.

You don't need to show the RandomSample field in the result.

Each time you run this query you will get a new random set of 100 records.

Note - If your table doesn't have a numerical Primary Key (Aaack!!) then just add one using an autonumber.

Cheers,

[RuralGuy]

Don't you have to execute a Randomize somewhere to change the seed?

[ConneXionLost]

Hi RG,

Not that I'm aware of. I set this up on a test database and the random numbers appeared to reset each time I ran the query. However, I certainly can't "guarantee" true randomness with this approach.

Cheers,

[RuralGuy]

Thanks for the update.