Random Selection of Records for mailing

[Guest]

From a list of 100,000 contacts, how would I select 1,000 records randomly?
The purpose of this is to mail out solicitations to various records from
different sections of the database instead of sequential selection.
Sequential selection would extract records from only one or two groups. I
would like a more varied result set spanning the entire database. Thank you.

 

[Guest]

Try this --
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.

For a different group use a different number than 4.6345679 in the formula.

 

[John W. Vinson]

From a list of 100,000 contacts, how would I select 1,000 records randomly?
The purpose of this is to mail out solicitations to various records from
different sections of the database instead of sequential selection.
Sequential selection would extract records from only one or two groups. I
would like a more varied result set spanning the entire database. Thank you.

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]

 

[Guest]

Thanks for Your reply. Please tell me how to put this into the query grid or
how to put it into SQL View and make it run. My table does have an
autonumber field.

Try this --
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.

For a different group use a different number than 4.6345679 in the formula.

--
KARL DEWEY
Build a little - Test a little

From a list of 100,000 contacts, how would I select 1,000 records randomly?
The purpose of this is to mail out solicitations to various records from
different sections of the database instead of sequential selection.
Sequential selection would extract records from only one or two groups. I
would like a more varied result set spanning the entire database. Thank you.

 

[Guest]

Thanks for your reply. I was able to put your reply into action, but with
one question. How do I sort the query by Shuffle? I Clicked the Properties
button on the toolbar and managed to find the Top Values property. Is this
where I set the number of records I want? Thanks.

From a list of 100,000 contacts, how would I select 1,000 records randomly?
The purpose of this is to mail out solicitations to various records from
different sections of the database instead of sequential selection.
Sequential selection would extract records from only one or two groups. I
would like a more varied result set spanning the entire database. Thank you.

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]

 

[John W. Vinson]

Thanks for your reply. I was able to put your reply into action, but with
one question. How do I sort the query by Shuffle?

Select either Ascending or Descending (it doesn't matter which!) on the Order
By row in the query grid underneath Shuffle.

I Clicked the Properties
button on the toolbar and managed to find the Top Values property. Is this
where I set the number of records I want? Thanks.

Yes.

John W. Vinson [MVP]