Randomly choose records

[Stephen Simons]

Help!

Access 2000

I'm an Access newbie, although I am an advanced XL user.

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?

TIA

Steve
remove "_ILY_" to email me

email to (e-mail address removed)

 

[John Vinson]

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.

 

[Stephen Simons]

John

Many, many thanks for this advice. Although I have practically no experience of Access I managed to get it to work (with
quite a few errors along the way, whcih helped me learn more!).

I'm going to get it working with my DBS over the next week or so. I may need some more help then, if that's OK?

Steve

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.

remove "_ILY_" to email me

email to (e-mail address removed)

 

[John Vinson]

John

Many, many thanks for this advice. Although I have practically no experience of Access I managed to get it to work (with
quite a few errors along the way, whcih helped me learn more!).

I'm going to get it working with my DBS over the next week or so. I may need some more help then, if that's OK?

Sure - just post a message here, there are quite a few good folks
who'll be glad to help out.