Prev: Problem with query
Next: Is there anything wrong with the syntax
From: Mia on 12 May 2010 17:08
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

From: John W. Vinson on 12 May 2010 17:35
On Wed, 12 May 2010 14:08:01 -0700, Mia <Mia(a)discussions.microsoft.com> 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]
 | 
Pages: 1
Prev: Problem with query
Next: Is there anything wrong with the syntax