How do I do a random sample query of data in an Access database?

[Guest]

I'm trying to write a query in Microsoft Access database that will give me a
random statistical sample from the data in the table. For example I have
roughly 700,000 records of data in a table that I want to capture a random
stat sample of 400 records.

Thanks,

 

[Rick B]

Have you done a search? This same type of question is asked pretty often.

 

[Michel Walsh]

Hi,


SELECT TOP 400 *
FROM myTable
ORDER BY Rnd( NumericalFieldHere )


If you use
SELECT Rnd( ), *
FROM myTable

the same random value will be repeated for all the records. An expression
that does not involve a field is considered a "constant" and the optimizer
computes it just once. So, to get a different random value for each record,
we used Rnd( fieldName ).



Hoping it may help,
Vanderghast, Access MVP

 

[Nikos Yannacopoulos]

Start by adding an extra numeric (double) field in the table, and call
it, say, RandID.
Next, make an update query on the table, to update this field using
function Rnd(), which returns random numbers between 0 and 1. Use any
nymeric field in the table as an argument in the Rnd() function, like:
Rnd([SomeField])
so you force the function to return a different value in every recor
(otherwise the function is only called once, and you get the same value
in alll records). Save the query.
Finally, make a select query on the table, sort on the RandID field and
set its Top Values property to 400.
Having done the above, every time you want a new sample, run the update
query (so you generate new random values) and pick your 400 random
records from the select query.

HTH,
Nikos

 

[Nikos Yannacopoulos]

Hey, ignore this, see Michael Walsh's reply, it's much better!

Nikos

 

[Guest]

This helped me also. Thanks a million!

Hi,


SELECT TOP 400 *
FROM myTable
ORDER BY Rnd( NumericalFieldHere )


If you use
SELECT Rnd( ), *
FROM myTable

the same random value will be repeated for all the records. An expression
that does not involve a field is considered a "constant" and the optimizer
computes it just once. So, to get a different random value for each record,
we used Rnd( fieldName ).



Hoping it may help,
Vanderghast, Access MVP