At random selection

[IceCube]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"IceCube" <an*********@pandora.be> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

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

[IceCube]

Thanks. I've also got the following interesting links.
Maybe for the next one with the same problem?

http://www.fontstuff.com/vba/Vbatut02pfv.htm
www.webbes.dds.nl (download/notsoeasysamples