At random selection of 30 records in a table of 1500 records

[Guest]

Hello,

I would like to select/filter at random 30 records out of an Access-table of
1500 records. I know the option "Top" which gives me the possibility to see
the 30 first records of the table The problem is that this option is linked
to the way of sorting the table. So you will always select from a very
restraint part of the 1500 records.

Is there a possibility
* to select or sort at random in Access. Maybe there exists an sql-syntax?
* to use a similar function like the funciton Aselect in Excel, which gives
me the possibility to get an number at random

That's the way I resolve at this moment my problem. I export my table to
Excel, I accord an number at random (using the function Aselect) to each
record, I import again in Access and I sort on the number, before using the
function Top. The next time, I use the same procedure to get another set of
records.

I assume there must be an easier way to do it?

Is there anybody who knows another solution?

Thanks in advance

 

[Allen Browne]

This query assumes your table is named "Table1", and it has an AutoNumber
field named "ID":

SELECT TOP 30 Table1.*
FROM Table1
ORDER BY Rnd(Table1.ID), Table1.ID;

For that to be random, you need to issue a Randomize in VBA code before
running the query.

The Rnd() doesn't do anything with the value passed in, but if you don't
pass something the query optimizer is too clever and doesn't bother calling
the function at every row.

The 2nd element of the ORDER BY clause is to prevent more than 30 records
being returned in the (unlikely) event that Rnd() creates duplicates.