Qry a random list of n items per dept from all items in 63 depts

[Obviously not a programmer :(]

I have a large list of users (approx 25,000) segmented by department (63
depts). I would like to randomly query 15 users from each department without
writing a separate query for all 63. My idea (and I'm not overly attached to
it at this point) was to create a field that assigns a random number to each
user, and then sample the lowest 15 users for each department, but I'm not
getting the right amount of users, and now I'm stuck and frustrated. Please
help!

 

[Chris2]

"Obviously not a programmer " <Obviously not a programmer
@discussions.microsoft.com> wrote in message

I have a large list of users (approx 25,000) segmented by department (63
depts). I would like to randomly query 15 users from each department without
writing a separate query for all 63. My idea (and I'm not overly attached to
it at this point) was to create a field that assigns a random number to each
user, and then sample the lowest 15 users for each department, but I'm not
getting the right amount of users, and now I'm stuck and frustrated. Please
help!

Obviously not a programmer ,

SQL (and RDBMs in general) will offer no meaningful help (that I can
think of) in making random selections.

You will need to write VBA code to cover this.

Via VBA, you can use an SQL statement to return a recordset that
represents each department, one at a time. You'll also use SQL to
return a count of the rows in that department.

The VBA will then generate fifteen random numbers between 1 and the
number of people in the department, using recursion to prevent the
creation of duplicate random numbers (or whatever scheme you prefer).

Then you can use that list of random numbers to pull out users from
the department recordset by looping through the recordset and pulling
a user when the loop counter equals one of the random numbers.

Repeat an outer loop once for each department.


Sincerely,

Chris O.