Getting random selection in a query

[awhitsel]

I am writing a query and I want to randomly pick out a set of 100 entries out of this query.

Is there any way to do this in the query (or the VBA code)?

Thanks.

 

[AWithers]

see my earlier thread333-332649

This code should do the trick

select top 100 yourid from yourtable
order by rnd(yourid)

Andy

 

[itoldie]

Here is a query that we use all the time for randomizing. The (cat_ups_mailing_1) is the table you are selecting from and the (tmp_mail_4) is the table you make to put your random selection into. The fields selected in the where and order by are the criteria for your random selection.

SELECT TOP 100 CAT_UPS_MAILING_1.* INTO TMP_MAIL_4
FROM CAT_UPS_MAILING_1
WHERE (((CAT_UPS_MAILING_1.PHONE) Is Not Null) AND ((randomizer())=0))
ORDER BY Rnd(IsNull(CAT_UPS_MAILING_1.CONTROL_NBR)*0+1);

Then you make a module with this code.
Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function