On Wed, 12 May 2010 14:08:01 -0700, Mia wrote:

Hi
could you please help, I am new to Access

I have 60 Brands and each Brand have more than 2K customers

I am trying to pick 40 customer from each brand randomly, and I have failed

fields in the table

Customer #
email
Brand ID

You help is much appreciated

thanks

You can use the Top Values property of a subquery, 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 use a Query

SELECT [Customer #], [email], [Brand ID]
FROM table
WHERE [Customer #] IN
(SELECT TOP 40 X.[Customer #] FROM table AS X
WHERE X.[Brand ID] = table.[Brand ID]
ORDER BY RndNum([Customer #]);

Untested air code, post back if you have problems with it!
--

John W. Vinson [MVP]