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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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,
 
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
 
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
 
This helped me also. Thanks a million!

Michel Walsh said:
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
Click to expand...
 
Back
Top