Random Selection of Records

[carl]

I have a table named Table1 that has 200,000 records. Is there a way to
randomly select 40,000 records from this table ?

Thank you in advance.

 

[Marshall Barton]

I have a table named Table1 that has 200,000 records. Is there a way to
randomly select 40,000 records from this table ?

In your code, use the Randomize statement before using the
query:

SELECT TOP 40000 *
FROM Table1
ORDER BY Rnd(1)

 

[John W. Vinson]

I have a table named Table1 that has 200,000 records. Is there a way to
randomly select 40,000 records from this table ?

Thank you in advance.

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.

 

[Marshall Barton]

I have a table named Table1 that has 200,000 records. Is there a way to
randomly select 40,000 records from this table ?

Thank you in advance.

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.

Good one, John. I completely forgot the part about Rnd
needing a field argument.

 

[carl]

Thanks Marshall and John.
I am not an experienced Access person and have some trouble coming up with
the query that you recoomended. Here's what I have - it does not seem to
produce the result I need - a list of 40000 randomly selected items.

SELECT TOP 40000 RndNum([GroupName]) AS Shuffle, *
FROM Table1
ORDER BY Rnd(1);

I really appreciate your help.

I have a table named Table1 that has 200,000 records. Is there a way to
randomly select 40,000 records from this table ?

Thank you in advance.

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.

 

[Marshall Barton]

Thanks Marshall and John.
I am not an experienced Access person and have some trouble coming up with
the query that you recoomended. Here's what I have - it does not seem to
produce the result I need - a list of 40000 randomly selected items.

SELECT TOP 40000 RndNum([GroupName]) AS Shuffle, *
FROM Table1
ORDER BY Rnd(1);

SELECT TOP 40000 *
FROM Table1
ORDER BY RndNum([GroupName])

There is no need to put the RndNum([GroupName]) AS Shuffle
in the select list and it would be meaningless to your
program.