select random records

[mcourter]

anybody have a good way to select random records?

i need to be able to either tell how many records i need or enter a
percentage as a value.
i found this <http://support.microsoft.com/?kbid=210616> but it is very
slow.

thanks,
mcnewsxp

 

[KARL DEWEY]

Duplicate you table structure and add an Autonumber field and set New Values
to Random. Append your data. Have your select query sort that field and
pull only the number of records you want.

 

[John Vinson]

anybody have a good way to select random records?

i need to be able to either tell how many records i need or enter a
percentage as a value.
i found this <http://support.microsoft.com/?kbid=210616> but it is very
slow.

thanks,
mcnewsxp

A simpler solution is to write a tiny dumb wrapper function:

Public Function RndNum(vIgnore As Variant) As Double
Static bInitiated As Boolean
If Not bInitiated Then
bInitiated = True
Randomize ' but only once
End If
RndNum = Rnd()
End Function

Then use a Query

SELECT TOP 10 ' or TOP 10 PERCENT
fielda, fieldb, fieldc
FROM yourtable
ORDER BY RndNum([fielda]);

You need to pass some argument to RndNum so Access will know to call
it for each row - if you don't, it "saves time" by calling the
function once and then using the same value on every row.

This WILL still be slow, because it must retrieve all the records in
the table, sort them by a non-indexed function call, and then
discarding all that you don't want to keep.

John W. Vinson[MVP]