Random Selection Of Records

[Tom]

Say I have a field named Numbers in MyTable and the field contains 500
random numbers between 1 and 1000; no duplicates. How do you write a query
to randomly return 50 records with no duplicates from the 500 records in the
table?

Thanks!

Tom

 

[Allen Browne]

Try this query:
SELECT TOP 50 MyTable.*
FROM MyTable
ORDER BY Rnd(MyTable.MyID), MyTable.MyID;

Before running it you need to execute a Randomize statement so Rnd() does
not use a repeatable sequence.

 

[John Vinson]

Say I have a field named Numbers in MyTable and the field contains 500
random numbers between 1 and 1000; no duplicates. How do you write a query
to randomly return 50 records with no duplicates from the 500 records in the
table?

Use the Top Values property:

SELECT TOP 50 *
FROM MyTable
ORDER BY [Numbers];

If you mean no duplicates *between runs of the query* then it's much
more difficult!

John W. Vinson[MVP]