Random, Limited, Record Select Query

[sck501]

I have a database with 50,000+ records. There are times when we want to do test mailings to a limited group of people (for example, 200 of the 50,000 will receive this mailing). We normally want this group to be a random selection from our database or a subgroup of the database. How can I set up a query to randomly pull the names and addresses of x number of people?

Steve

 

[JoeMiller]

I have a sample DB that will show you how to do it if you send me your email address.
Joe Miller
joe.miller@flotech.net

 

[DougP]

Public Sub RandomPicker(Howmany)
'Two things to do if you can
'1st add a unique ID field (an AutoNumber field)
'2nd add a Yes/No field to track what you have sent already

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection ' <<<<Note same as CurrentDb
Dim Rs1, Rs2 As ADODB.Recordset
Dim SQLCode, SQLCode2 As String
Dim TotRecs As Single
Set Rs1 = New ADODB.Recordset
Set Rs2 = New ADODB.Recordset
SQLCode = &quot;Select * From yourTable;&quot;
Rs1.Open SQLCode, cnn, adOpenStatic, adLockOptimistic

Rs1.MoveLast
Rs1.MoveFirst
TotRecs = Rs1.RecordCount
'Randomly get some records
For a = 1 To Howmany
'MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.
WhichRec = Format(TotRecs * Rnd + 1, &quot;####.&quot
SQLCode2 = &quot;UPDATE yourTable SET Sent = -1 WHERE UniqueID = &quot; & WhichRec & &quot;;&quot;
Rs2.Open SQLCode2, cnn, adOpenStatic, adLockOptimistic
Next
Set Rs1 = Nothing
Set Conn2 = Nothing

End Sub

-------------
this code does not check to see if a record has been selected already.
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[JoeMiller]

My sample checks to make sure that a record is already selected, which would make a difference in your application. It involves a simple function and a subquery within another query.

Joe Miller
joe.miller@flotech.net

 

[DougP]

the very last line above should be
set cnn = nothing

Call this like so:
Call RandomPicker(200)
the 200 is the number of mailings you want to send out

then run a query on your table and criteria for the &quot;Sent&quot; field to equal True

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive