Selecting Random Rows

[Guest]

Hello,

What would be the syntax to select random 500 rows from a table containing
40K rows?

Thank you for all the help.

Regards,

 

[Guest]

SELECT TOP X YourFieldNames, ...
Replace the "X" with the quanity of records you want.

If your table has an autonumber field then you can use a calculated field to
simulate a random number.
Right(Left((4.6345679/ [AutoNUM]) *10,000)5),3)
Sort it ascending.

 

[John W. Vinson]

Hello,

What would be the syntax to select random 500 rows from a table containing
40K rows?

You can use the Top Values property of a query, with help
from a little VBA. Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see.

John W. Vinson [MVP]