Random Sample?

Topic

New Reply

I have a table in MS Access 2003 with customer records for a laptop manufacurer and I need Access to select a sub set of those customers completely randomly.

The table has 84,475 records and I need a new table with a random 2000 records. It cannot be first 2000 or anything like that – can I pull a random sample?

Is it a query property?

I think this can be done and I am lost – thanks again for Woody’s!

Mike D.

Reply | Quote

Replies

I have not tested this BUT….

Assuming you have an Identity (AutoNumber field) in the table

Then something like this ought to work

Code:

Select TOP 2000  IDField, Rnd([IDField]) As RField,Field2, Field3, Field4, etc
FROM TableName
ORDER BY Rnd([IDField]) DESC

Obviously, you can play around with sort order etc

Reply | Quote

It actually doesn’t matter what the arg is to Rnd, as long as it’s based on the record. Even if it evaluates to the same value, it forces Rnd to re-evaluate for each record.
You don’t need an ID or AutoNumber field.

Code:

Select TOP 2000  IDField, Rnd(Len([PersonsName])) As RField,Field2, Field3, Field4, etc
FROM TableName
ORDER BY Rnd(Len([PersonsName])) DESC

Reply | Quote