Extracting random records from a table (nthing)

[Shirley E]

I want to know if it is possible to nth a list in ACCESS?

 

[Jim/Chris]

I go this from a posting by John Vinson a while back. John
really know his stuff. I have not used it yet. Hope it
helps you.

From a dbs containing several thousand records, I would

like to be able to choose all that meet a certain criteria (say

one field is equal to, or greater than, zero); and then

from the records that meet that criteria, randomly select a

sample of say 20.

I can achieve this relatively easily in XL, using formulae and some VBA code.

Can anyone point me towards the right approach for Access?

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]


Jim

 

[John Vinson]

I want to know if it is possible to nth a list in ACCESS?

Explicate? I'm not familiar with "nthing"...

You can extract random records though: create a little function by
editing into a Module named basRandom:

Public Function RandNum(vIgnore As Variant) As Double
Static bRandomized As Boolean
If Not bRandomized Then
Randomize ' initialize the random number generator
bRandomized = True
End If
RandNum = Rnd()
End Function

Then in your Query put a calculated field

Shuffle: RandNum([somefield])

where somefield is any field in your table; this will force Access to
call the function for every row.

Sort by this field, and use the TOP VALUES property of the query to
return the top 100, or top 10%, or whatever number of records you
want.

 

[Jim/Chris]

John,

I keep most everything you post. Thanks for your knowledge
and experience. It has helped me tremendously.

Thanks again

Jim

-----Original Message-----

I want to know if it is possible to nth a list in ACCESS?

Explicate? I'm not familiar with "nthing"...

You can extract random records though: create a little function by
editing into a Module named basRandom:

Public Function RandNum(vIgnore As Variant) As Double
Static bRandomized As Boolean
If Not bRandomized Then
Randomize ' initialize the random number generator
bRandomized = True
End If
RandNum = Rnd()
End Function

Then in your Query put a calculated field

Shuffle: RandNum([somefield])

where somefield is any field in your table; this will force Access to
call the function for every row.

Sort by this field, and use the TOP VALUES property of the query to
return the top 100, or top 10%, or whatever number of records you
want.


.